如何使用sql查询水平地从明细表中选择垂直数据

本文关键字：明细表 选择 垂直 数据 何使用 sql 查询 水平 | 更新日期: 2023-09-27 18:26:09

之前可能有人问过类似的问题，但我找不到。这就是我的目标。我有三张像1.Bill 2.BillDetail 3.ExpenseInfo一样的桌子。表"Bill"有一个名为"BillID"的主键，它是表"BillDetail"的外键。类似地，表"ExpenseInfo"有一个PK"ExpenseID"，也是"BillDetail"的FK。这些表格中保存的数据格式如下。For 1 entry in Bill table, there might be 1 or more rows in BillDetail table where each row contains 1 ExpenseID。现在我想写一个查询，它将选择类似于-的数据

BillID BillDate Fuel Food Travel
  1    28-02-12  10   20    50

这里的燃料、食物、旅行是"ExpenseInfo"表中保存的各种费用类型。我在下面试过了。我走的是正确的路还是其他明智的方式？

SELECT * 
FROM t_BillInfoDetail a 
LEFT OUTER JOIN 
(
SELECT ISNULL(ExpenseID,0) AS ExpenseID, ISNULL(ExpenseName,'N/A') AS ExpenseName    
FROM t_expenseinfo 
WHERE ExpenseID=1 
) AS LocalTravel ON a.ExpenseID = LocalTravel.ExpenseID

新增感谢大家的回复。目前，出于我的目的，我正在做以下事情。希望你喜欢。

SELECT 
BI.* , BID.ExpenseID, BID.BillDescription, BID.LocalTravel, BID.LocalHotel, BID.Fuel  
FROM  
(
SELECT * 
FROM t_BillInfo 
WHERE BillID = 1 
) AS BI 
LEFT OUTER JOIN 
(
SELECT BillID, BillDescription, ExpenseID, 
    ISNULL(SUM(CASE ExpenseID WHEN 1 THEN Amount ELSE 0 END), 0) AS LocalTravel,
    ISNULL(SUM(CASE ExpenseID WHEN 2 THEN Amount ELSE 0 END), 0) AS LocalHotel,
    ISNULL(SUM(CASE ExpenseID WHEN 3 THEN Amount ELSE 0 END), 0) AS Fuel  
FROM t_BillInfoDetail 
GROUP BY BillID, BillDescription, ExpenseID  
) AS BID ON BI.BillID = BID.BillID

如何使用sql查询水平地从明细表中选择垂直数据

也许我没有答对这个问题，但在我看来，有3个表像"链"Bill -HAS_MANY> BillDetail -HAS_ONE> ExpenseInfo一样连接在一起。

如果是这种情况，您只需要使用内部联接来联接表，请求来自所需的每个表的数据。你没有提供你的表格结构，但应该这样做：

select b.aBillField, bd.aBillDetailField, ei.aExpenseInfoField from bill b
inner join billDetail bd
on b.billID = bd.billFK
inner join expenseInfo ei
on ei.expenseFK = bd.billDetailId
where expenseId = 1

当然，用相应的字段替换发明的字段。

如果你需要一个存储过程，如果你能更清楚地回答你的问题，我们可以很容易地回答你。。。现在你试试这个

  create procedure [dbo].[SP_GetInfo]
   (
    @ExpenseID int
   )
  as 
  begin
  select Bill .BillID , BillDetail .BillDate , ExpenseInfo.Fuel 
  from Bill , BillDetail , ExpenseInfo
  where Bill .BillID = BillDetail .BillID       
  AND ExpenseInfo.ExpenseID  =@ExpenseID
 GO

试试这个，如果你有任何错误，请问我…

如果我读得正确，您希望在ExpenseInfo表中PIVOT您的数据。尝试以下操作：

SELECT BillID,
       BillDate,
       Fuel,
       Food,
       Travel
FROM   (SELECT BillID,
               BillDate,
               ExpenseName,
               ExpenseAmount
        FROM   Bill AS B
               INNER JOIN BillDetail BD
                 ON B.BillID = BD.BillID
               INNER JOIN ExpenseInfo EI
                 ON BD.ExpenseID = EI.ExpenseID) AS up PIVOT (SUM( ExpenseAmount
       ) FOR
       ExpenseName IN (Fuel, Food, Travel)) AS pvt
ORDER  BY BillID

这就是我正在使用的。它满足了我的要求。

SELECT 
BI.* , BID.ExpenseID, BID.BillDescription, BID.LocalTravel, BID.LocalHotel, BID.Fuel  
FROM  
(
SELECT * 
FROM t_BillInfo 
WHERE BillID = 1 
) AS BI 
LEFT OUTER JOIN 
(
SELECT BillID, BillDescription, ExpenseID, 
    ISNULL(SUM(CASE ExpenseID WHEN 1 THEN Amount ELSE 0 END), 0) AS LocalTravel,
    ISNULL(SUM(CASE ExpenseID WHEN 2 THEN Amount ELSE 0 END), 0) AS LocalHotel,
    ISNULL(SUM(CASE ExpenseID WHEN 3 THEN Amount ELSE 0 END), 0) AS Fuel  
FROM t_BillInfoDetail 
GROUP BY BillID, BillDescription, ExpenseID  
) AS BID ON BI.BillID = BID.BillID
相关文章：