帮助创建复杂的存储过程

| 因此，我什至不确定这是否可行，但学习并能够做到这一点确实很棒。 我想做的是运行3个单独的查询，这些查询返回一行数据并创建一个表，然后可以在asp.net的gridview中使用该表 查询1 
SELECT     dbo.BOOKINGS.USERID, SUM(dbo.BOOKINGS.APRICE) AS total, COUNT
(dbo.BOOKINGS.USERID) AS bookingcount
FROM         dbo.BOOKINGS INNER JOIN
             dbo.TOURS ON dbo.BOOKINGS.TOUR = dbo.TOURS.TOUR INNER JOIN
             dbo.MAJOR ON dbo.TOURS.MAJOR = dbo.MAJOR.MAJOR
WHERE     (dbo.BOOKINGS.BOOKED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
@enddate)) AND (dbo.MAJOR.SDESCR = \'Cruises\') AND 
                      (dbo.BOOKINGS.USERID = @USER) AND (dbo.MAJOR.DIVISION = \'A\')
GROUP BY dbo.BOOKINGS.USERID
查询2 
SELECT     dbo.BOOKINGS.USERID, SUM(dbo.BOOKINGS.APRICE) AS total, COUNT
    (dbo.BOOKINGS.USERID) AS bookingcount
FROM         dbo.BOOKINGS INNER JOIN
                      dbo.TOURS ON dbo.BOOKINGS.TOUR = dbo.TOURS.TOUR INNER JOIN
                      dbo.MAJOR ON dbo.TOURS.MAJOR = dbo.MAJOR.MAJOR
WHERE     (dbo.BOOKINGS.BOOKED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
    @enddate)) AND (dbo.MAJOR.SDESCR <> \'Cruises\') AND 
                      (dbo.BOOKINGS.USERID = @USER) AND (dbo.MAJOR.DIVISION = \'A\')
GROUP BY dbo.BOOKINGS.USERID
查询3 
SELECT     SUM(dbo.SUBS.AMOUNT) AS total, COUNT(dbo.SUBS.AMOUNT) AS Memberships, 
    dbo.HOMES.USERID
FROM         dbo.HOMES INNER JOIN
                      dbo.SUBS ON dbo.HOMES.HOME = dbo.SUBS.HOME AND dbo.HOMES.JOINED 
    = dbo.SUBS.PAIDON
WHERE     (dbo.HOMES.JOINED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
    @enddate)) AND (dbo.HOMES.USERID = 
    @USER)
GROUP BY dbo.HOMES.USERID
所有查询均返回包含3列的一行 所以我认为这可以工作的唯一其他困难部分是我想添加一个新列 
query1 userid  total1   bookingcount
query2 userid  total2   bookingcount
query3 userid  total3   memberships
    
2019-12-15 3 条评论
分享

没有找到相关结果

    已邀请:

    磁辫覆氓

    您可以使用UNION语句来执行此操作，您要做的就是获取3个select语句，以相同的顺序返回相同数量的字段 
    SELECT     \'QUERY1\' AS QUERYNAME, dbo.BOOKINGS.USERID, SUM(dbo.BOOKINGS.APRICE) AS total, COUNT(dbo.BOOKINGS.USERID) AS TOTAL2
FROM         dbo.BOOKINGS INNER JOIN
             dbo.TOURS ON dbo.BOOKINGS.TOUR = dbo.TOURS.TOUR INNER JOIN
             dbo.MAJOR ON dbo.TOURS.MAJOR = dbo.MAJOR.MAJOR
WHERE     (dbo.BOOKINGS.BOOKED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
@enddate)) AND (dbo.MAJOR.SDESCR = \'Cruises\') AND 
                      (dbo.BOOKINGS.USERID = @USER) AND (dbo.MAJOR.DIVISION = \'A\')
GROUP BY dbo.BOOKINGS.USERID

UNION ALL

SELECT     \'QUERY2\' AS QUERYNAME, dbo.BOOKINGS.USERID, SUM(dbo.BOOKINGS.APRICE) AS total, COUNT(dbo.BOOKINGS.USERID) AS TOTAL2
FROM         dbo.BOOKINGS INNER JOIN
                      dbo.TOURS ON dbo.BOOKINGS.TOUR = dbo.TOURS.TOUR INNER JOIN
                      dbo.MAJOR ON dbo.TOURS.MAJOR = dbo.MAJOR.MAJOR
WHERE     (dbo.BOOKINGS.BOOKED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
    @enddate)) AND (dbo.MAJOR.SDESCR <> \'Cruises\') AND 
                      (dbo.BOOKINGS.USERID = @USER) AND (dbo.MAJOR.DIVISION = \'A\')
GROUP BY dbo.BOOKINGS.USERID

UNION ALL

SELECT         \'QUERY3\' AS QUERYNAME, dbo.HOMES.USERID, SUM(dbo.SUBS.AMOUNT) AS total, COUNT(dbo.SUBS.AMOUNT) AS TOTAL2
FROM         dbo.HOMES INNER JOIN
                      dbo.SUBS ON dbo.HOMES.HOME = dbo.SUBS.HOME AND dbo.HOMES.JOINED 
    = dbo.SUBS.PAIDON
WHERE     (dbo.HOMES.JOINED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
    @enddate)) AND (dbo.HOMES.USERID = 
    @USER)
GROUP BY dbo.HOMES.USERID
        
    2019-12-15 0 0
    分享

    羔磺

    这看起来像一个标准的UNION-尽管您需要按相同的顺序排列列。科学来了（这会很长） 
    SELECT  \'query1\',   dbo.BOOKINGS.USERID, SUM(dbo.BOOKINGS.APRICE) AS total, COUNT
(dbo.BOOKINGS.USERID) AS bookingcount
FROM         dbo.BOOKINGS INNER JOIN
             dbo.TOURS ON dbo.BOOKINGS.TOUR = dbo.TOURS.TOUR INNER JOIN
             dbo.MAJOR ON dbo.TOURS.MAJOR = dbo.MAJOR.MAJOR
WHERE     (dbo.BOOKINGS.BOOKED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
@enddate)) AND (dbo.MAJOR.SDESCR = \'Cruises\') AND 
                      (dbo.BOOKINGS.USERID = @USER) AND (dbo.MAJOR.DIVISION = \'A\')
GROUP BY dbo.BOOKINGS.USERID
UNION
SELECT    \'query2\', dbo.BOOKINGS.USERID, SUM(dbo.BOOKINGS.APRICE) AS total, COUNT
    (dbo.BOOKINGS.USERID) AS bookingcount
FROM         dbo.BOOKINGS INNER JOIN
                      dbo.TOURS ON dbo.BOOKINGS.TOUR = dbo.TOURS.TOUR INNER JOIN
                      dbo.MAJOR ON dbo.TOURS.MAJOR = dbo.MAJOR.MAJOR
WHERE     (dbo.BOOKINGS.BOOKED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
    @enddate)) AND (dbo.MAJOR.SDESCR <> \'Cruises\') AND 
                      (dbo.BOOKINGS.USERID = @USER) AND (dbo.MAJOR.DIVISION = \'A\')
GROUP BY dbo.BOOKINGS.USERID
UNION
SELECT  \'query3\',dbo.HOMES.USERID, SUM(dbo.SUBS.AMOUNT) AS total, 
        COUNT(dbo.SUBS.AMOUNT) AS Memberships    
FROM         dbo.HOMES INNER JOIN
                      dbo.SUBS ON dbo.HOMES.HOME = dbo.SUBS.HOME AND dbo.HOMES.JOINED 
    = dbo.SUBS.PAIDON
WHERE     (dbo.HOMES.JOINED BETWEEN CONVERT(int, @startdate) AND CONVERT(int, 
    @enddate)) AND (dbo.HOMES.USERID = 
    @USER)
GROUP BY dbo.HOMES.USERID
        
    2019-12-15 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.039854049682617