在sql中加入多个select语句的结果

I have four select statements and I want to join them all to only get the common rows.

我有四个select语句,我想将它们全部加入以获取公共行。

In an example, I'm providing 2 select statements:

在一个例子中,我提供了2个select语句:

SELECT 
    h.userid, 'Activity' as table_name, 
    h.stamp, 
    DATEDIFF(dd, kh.LatestDate, GETDATE()) as days_since, 
    m.group_name
FROM 
    ([Animal].[SYSADM].[activity_history] h
INNER JOIN 
    (SELECT userid, MAX(stamp) as LatestDate
     FROM [Animal].[SYSADM].[activity_history]
     GROUP BY userid) kh ON h.userid = kh.userid AND h.stamp = kh.LatestDate)  
LEFT OUTER JOIN 
    [Animal].[SYSADM].secure_member m ON m.user_name = h.userid
WHERE 
    (DATEDIFF(dd, kh.LatestDate, GETDATE()) > 90)
    AND NOT (m.group_name = 'inactive')
ORDER BY 
    userid

SELECT 
    h.userid, 'Person' as table_name, h.stamp, 
    DATEDIFF(dd, kh.LatestDate, GETDATE()) as days_since, 
    m.group_name
FROM 
    ([Animal].[SYSADM].[person_history] h
INNER JOIN 
    (SELECT userid, max(stamp) as LatestDate
     FROM [Animal].[SYSADM].[person_history]
     GROUP BY userid) kh ON h.userid = kh.userid AND h.stamp = kh.LatestDate)  
LEFT OUTER JOIN 
    [Animal].[SYSADM].secure_member m ON m.user_name = h.userid
WHERE 
    (DATEDIFF(dd, kh.LatestDate, GETDATE()) > 90)
    AND NOT (m.group_name = 'inactive')
ORDER BY 
    userid

I have tried INTERSECT, but it's not returning any rows, I want to see the common rows from both the select statements (actually I have 4 so I believe what works for 2 will work for 4)

我已经尝试过INTERSECT,但它没有返回任何行,我想看到两个select语句中的常见行(实际上我有4个,所以我相信适用于2的那个将适用于4)

Thanks in advance.

提前致谢。

Update:

I tried inner join on 2 select statements and it gave me the desired result but now the question is how I can use inner join on 4 select statements.

我在2个select语句中尝试了内连接,它给了我想要的结果,但现在的问题是如何在4个select语句中使用内连接。

SELECT DISTINCT t1.userid as A_UserID, t2.userid as P_UserID, t1.stamp as A_stamp, t2.stamp as P_stamp, datediff(dd,t1.stamp,GetDate()) as A_days_since, datediff(dd,t2.stamp,GetDate()) as P_days_since, t1.group_name, t1.table_name, t2.table_name
from 
    (SELECT h.userid, 'Activity' as table_name, h.stamp, datediff(dd,kh.LatestDate,GetDate()) as days_since, m.group_name
  FROM 
 ( [Animal].[SYSADM].[activity_history] h
inner join (
    select userid, max(stamp) as LatestDate
  from [Animal].[SYSADM].[activity_history]
  group by userid
  ) kh on h.userid = kh.userid and h.stamp = kh.LatestDate
  )  
left outer join [Animal].[SYSADM].secure_member m on m.user_name = h.userid
where 
(datediff(dd,kh.LatestDate, GetDate()) > 90)
and not (m.group_name = 'inactive')) t1

inner join

    (SELECT h.userid, 'Person' as table_name, h.stamp, datediff(dd,kh.LatestDate,GetDate()) as days_since, m.group_name
  FROM 
 ( [Animal].[SYSADM].[person_history] h
inner join (
    select userid, max(stamp) as LatestDate
  from [Animal].[SYSADM].[person_history]
  group by userid
  ) kh on h.userid = kh.userid and h.stamp = kh.LatestDate
  )  
left outer join [Animal].[SYSADM].secure_member m on m.user_name = h.userid
where 
(datediff(dd,kh.LatestDate, GetDate()) > 90)
and not (m.group_name = 'inactive')) t2
on
    t1.userid = t2.userid
    order by T1.userid

Query Result

1 个解决方案

#1

Forget about the UNION for a moment. Imagine you take that result and insert into Table1

暂时忘掉UNION吧。想象一下,您获取该结果并插入Table1

Then depend what you mean the "common rows". If you want exact value but in different tables

然后取决于你所说的“常见行”。如果您想要精确值但在不同的表中

  SELECT userid, h.stamp, days_since, m.group_name
  FROM Table1
  GROUP BY userid, h.stamp, days_since, m.group_name
  HAVING COUNT( table_name ) = 2 -- in this case 2 because are two types 
                                 -- Activity and Persons

After viewing your query result you also need to add DISTINCT to each of the queries on the UNION.

查看查询结果后,还需要将DISTINCT添加到UNION上的每个查询中。

#1