sql存储过程，将行与派生变量相结合

我已经离开了一段时间，需要帮助，什么可能是一个简单的解决办法，但在这个时候，我卡住了。我有这个select语句，需要为同一个部门合并具有相似元素的行，但是GROUPBY会有一个非常大的select列表，有没有其他类似merge的方法？下面是我正在处理的sql语句以及输出：

SELECT distinct sg.Description as 'Groups', si.Store_Name, a.Store_ID, si.Store_ID1 as 'Store #2', d.Department_ID as 'Dept', a.Vendor_ID,
 IIF(d.Delivery_Day = 1, dbo.DayOfWeekToString(d.Order_Day),null) as 'Sun',
 IIF(d.Delivery_Day = 1, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 2, dbo.DayOfWeekToString(d.Order_Day),null) as 'Mon',
 IIF(d.Delivery_Day = 2, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 3, dbo.DayOfWeekToString(d.Order_Day),null) as 'Tue',
 IIF(d.Delivery_Day = 3, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 4, dbo.DayOfWeekToString(d.Order_Day),null) as  'Wed',
 IIF(d.Delivery_Day = 4, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 5, dbo.DayOfWeekToString(d.Order_Day),null) as  'Thu',
 IIF(d.Delivery_Day = 5, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 6, dbo.DayOfWeekToString(d.Order_Day),null) as  'Fri',
 IIF(d.Delivery_Day = 6, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)) ,null ) as 'time',
 IIF(d.Delivery_Day = 7, dbo.DayOfWeekToString(d.Order_Day),null)  as  'Sat',
 IIF(d.Delivery_Day = 7, CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2)),null) as 'time'
 from Delivery_Schedule_Header a  Join Vendor_Info b ON (a.Vendor_ID = b.Vendor_ID) Join Delivery_Schedule_Detail d  on d.UID_Delivery = a.UID 
 Join Store_Info si on si.Store_ID = a.Store_ID Join Store_Group sg  on sg.GroupStore_UID = si.GroupStore_UID 
Where a.Vendor_ID = @Vendor_Id and si.GroupStore_UID = Case when @Group_Id = -1 then si.GroupStore_UID else @Group_Id end

结果是：

Groups  Store_Name  Store_ID    Store #2    Dept    Vendor_ID   Sun time    Mon time    Tue time    Wed time    Thu time    Fri time    Sat time
Balls   Price Chopper 18                18  18  3200    70038000    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Wednesday   11:00   NULL    NULL    NULL    NULL
Balls   Price Chopper 18                18  18  3200    70038000    NULL    NULL    NULL    NULL    Monday  11:00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
Balls   Price Chopper 18                18  18  3200    70038000    Saturday    11:00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

这三行需要合并在同一个部门抱歉，如果这是相当初级，但我已经有一段时间没有这种类型的工作，现在需要一个复习。谢谢

你想要圆锥聚合。这通过创建 group by 子句，该子句包含除要合并的列以外的所有列，然后用聚合函数（例如 MIN() 或者 MAX() :

SELECT 
    sg.Description as Groups, 
    si.Store_Name, 
    a.Store_ID, 
    si.Store_ID1 as Store_2, 
    d.Department_ID as Dept, 
    a.Vendor_ID,
    MAX(CASE WHEN d.Delivery_Day = 1 THEN dbo.DayOfWeekToString(d.Order_Day) END) as Sun,
    MAX(CASE 
        WHEN d.Delivery_Day = 1 
        THEN CONCAT(CONVERT(varchar, d.Deadline_Hour) , ':', RIGHT('00' + CONVERT(varchar, d.Deadline_Minute), 2))
    ) as Sun_time,
    ...
FROM Delivery_Schedule_Header a  
JOIN Vendor_Info b               ON a.Vendor_ID = b.Vendor_ID
JOIN Delivery_Schedule_Detail d  ON d.UID_Delivery = a.UID 
JOIN Store_Info si               ON si.Store_ID = a.Store_ID 
JOIN Store_Group sg              ON sg.GroupStore_UID = si.GroupStore_UID 
WHERE 
    a.Vendor_ID = @Vendor_Id 
    AND si.GroupStore_UID = CASE WHEN @Group_Id = -1 THEN si.GroupStore_UID ELSE @Group_Id END
GROUP BY 
    sg.Description as Groups, 
    si.Store_Name, 
    a.Store_ID, 
    si.Store_ID1 as Store_2, 
    d.Department_ID as Dept, 
    a.Vendor_ID

旁注：
我重写了 IIF() 要使用的表达式 CASE （后者是标准sql，而前者是特定于tsql的）
单引号应该用于字符串，而不是列标识符（尽管SQLServer允许这样做）
结果集中有多个同名列( time )，这不是一个好的做法

sql存储过程，将行与派生变量相结合

1条答案

相关问题

热门标签

最新问答