m*******g
发帖数: 3044 | 1 表很简单.就三列
ID, Date, Comment
ID 这列很长,许多ID#是重复出现的. Date 这列就是对应没个ID, 许多也是重复出现.
我想在COMMENT 这列实现对每ID,如果对应日期那列,不同的日期出先三次或以上,就是"
YES" ,否则就是"NO".
举个例子,假如ID 222, 有30行.但对应日期那拦只有04/15/2017, 04/18/2017 这俩个
日期.那么22对应的COMMENT 都是"NO". 如果出现三不同个日期,就是"YES"
好像用SELF JOIN 可以? | A********Z
发帖数: 18 | 2 不知道理解对没有。
1.
SELECT t1.ID
, t1.Date
, CASE WHEN t2.ID IS NULL THEN 'NO' ELSE 'YES' END AS Comment
FROM Table t1
LEFT JOIN (
SELECT ID, Date
FROM Table
GROUP BY ID, Date
HAVING COUNT(*)>=3
) t2 ON t1.ID=t2.ID AND t1.Date=t2.Date
2.
SELECT ID
, Date
, CASE WHEN (SELECT COUNT(*) FROM Table WHERE ID=t1.ID AND Date=t1.Date)>=
3 THEN 'YES' ELSE 'NO' END AS Comment
FROM Table t1 | s*********t
发帖数: 296 | 3 select ID, case when Count(distinct date)>=3 then 'Yes' else 'No' end as
Comment
from table
group by ID | r***o
发帖数: 1526 | 4 T-SQL的实现
SELECT ID FROM
(SELECT ID, COUNT(Date) OVER (PARTITION BY ID) AS DateCount
FROM (SELECT ID, Date FROM A GROUP BY ID, Date) T) T1
WHERE DateCount >=3 |
|
