select a.* from tb as a inner join tb as b on a.orderid=b.orderid and a.id!=b.id where a.id=1 group by a.date,a.id,a.orderid
create table ly (id int, dates varchar(12), orderid int)insert into ly select 1, '2013/7/10', 1 union all select 2, '2013/7/11', 1 union all select 3, '2013/7/12', 1 union all select 4, '2013/7/10', 2 union all select 5, '2013/7/11', 3 union all select 6, '2013/7/10', 4 union all select 7, '2013/7/11', 4 select a.* from ly a where a.dates between '2013/7/10' and '2013/7/12' and exists(select 1 from ly b where b.id<>a.id and b.dates between '2013/7/10' and '2013/7/12' and b.orderid=a.orderid) /* id dates orderid ----------- ------------ ----------- 1 2013/7/10 1 2 2013/7/11 1 3 2013/7/12 1 6 2013/7/10 4 7 2013/7/11 4(5 row(s) affected) */
DECLARE @StartDate DateTime, @EndDate DateTime SET @StartDate = '2013/7/10' SET @EndDate = '2013/7/11'-- PRINT DATEDIFF(day,@StartDate,@EndDate) CREATE TABLE #test (id int, dates varchar(12), orderid int)INSERT INTO #test SELECT 1, '2013/7/10', 1 UNION all SELECT 2, '2013/7/11', 1 UNION all SELECT 3, '2013/7/12', 1 UNION all SELECT 4, '2013/7/10', 2 UNION all SELECT 5, '2013/7/11', 3 UNION all SELECT 6, '2013/7/10', 4 UNION all SELECT 7, '2013/7/11', 4 SELECT t.* FROM #test t INNER JOIN (SELECT a.orderid FROM #test a WHERE a.dates BETWEEN @StartDate AND @EndDate GROUP BY a.orderid HAVING COUNT(DISTINCT a.dates) = DATEDIFF(day,@StartDate,@EndDate) + 1) s ON t.orderid = s.orderid WHERE t.dates BETWEEN @StartDate AND @EndDate DROP TABLE #test
CREATE TABLE #test (id int, dates varchar(12), orderid int) INSERT INTO #test SELECT 1, '2013/7/10', 1 UNION all SELECT 2, '2013/7/11', 1 UNION all SELECT 3, '2013/7/12', 1 UNION all SELECT 4, '2013/7/10', 2 UNION all SELECT 5, '2013/7/11', 3 UNION all SELECT 6, '2013/7/10', 4 UNION all SELECT 7, '2013/7/11', 4 SELECT orderid FROM #test GROUP BY orderid HAVING COUNT(dates)=DATEDIFF(day,'2013-7-10','2013-7-12') + 1 DROP TABLE #test
CREATE TABLE #test (id int, dates varchar(12), orderid int) INSERT INTO #test SELECT 1, '2013/7/10', 1 UNION all SELECT 2, '2013/7/11', 1 UNION all SELECT 3, '2013/7/12', 1 UNION all SELECT 4, '2013/7/10', 2 UNION all SELECT 5, '2013/7/11', 3 UNION all SELECT 6, '2013/7/10', 4 UNION all SELECT 7, '2013/7/11', 4 SELECT orderid FROM #test WHERE dates>='2013/7/10' AND dates<='2013/7/12' GROUP BY orderid HAVING COUNT(dates)=DATEDIFF(day,'2013-7-10','2013-7-12') + 1 DROP TABLE #test加个WHERE dates>='2013/7/10' AND dates<='2013/7/12'
--保证符合楼主要求 if object_id('tempdb..#test')>0 DROP TABLE #testCREATE TABLE #test (id int, dates varchar(12), orderid int) INSERT INTO #test SELECT 1, '2013/7/10', 1 UNION all SELECT 2, '2013/7/11', 1 UNION all SELECT 3, '2013/7/12', 1 UNION all SELECT 4, '2013/7/10', 2 UNION all SELECT 5, '2013/7/11', 3 UNION all SELECT 6, '2013/7/10', 4 UNION all SELECT 7, '2013/7/11', 4 UNION all SELECT 8, '2013/7/11', 4 UNION all SELECT 9, '2013/7/10', 5 UNION all SELECT 10, '2013/7/11', 5 UNION all SELECT 11, '2013/7/11', 5 UNION all SELECT 10, '2013/7/12', 5 select orderid from ( SELECT orderid,dates FROM #test a where dates in('2013/7/10','2013/7/11','2013/7/12') GROUP BY orderid,dates ) a GROUP BY orderid having count(*)=3/* 1 5 */
select * from tab_name where orderid in (select orderid from tab_name group by orderid having count(date)>=3)
where a.id=1
group by a.date,a.id,a.orderid
create table ly
(id int, dates varchar(12), orderid int)insert into ly
select 1, '2013/7/10', 1 union all
select 2, '2013/7/11', 1 union all
select 3, '2013/7/12', 1 union all
select 4, '2013/7/10', 2 union all
select 5, '2013/7/11', 3 union all
select 6, '2013/7/10', 4 union all
select 7, '2013/7/11', 4
select a.*
from ly a
where a.dates between '2013/7/10' and '2013/7/12'
and exists(select 1
from ly b
where b.id<>a.id and b.dates between '2013/7/10' and '2013/7/12'
and b.orderid=a.orderid) /*
id dates orderid
----------- ------------ -----------
1 2013/7/10 1
2 2013/7/11 1
3 2013/7/12 1
6 2013/7/10 4
7 2013/7/11 4(5 row(s) affected)
*/
SET @StartDate = '2013/7/10'
SET @EndDate = '2013/7/11'-- PRINT DATEDIFF(day,@StartDate,@EndDate)
CREATE TABLE #test
(id int, dates varchar(12), orderid int)INSERT INTO #test
SELECT 1, '2013/7/10', 1 UNION all
SELECT 2, '2013/7/11', 1 UNION all
SELECT 3, '2013/7/12', 1 UNION all
SELECT 4, '2013/7/10', 2 UNION all
SELECT 5, '2013/7/11', 3 UNION all
SELECT 6, '2013/7/10', 4 UNION all
SELECT 7, '2013/7/11', 4
SELECT t.* FROM #test t
INNER JOIN (SELECT a.orderid
FROM #test a
WHERE a.dates BETWEEN @StartDate AND @EndDate
GROUP BY a.orderid
HAVING COUNT(DISTINCT a.dates) = DATEDIFF(day,@StartDate,@EndDate) + 1) s
ON t.orderid = s.orderid
WHERE t.dates BETWEEN @StartDate AND @EndDate DROP TABLE #test
CREATE TABLE #test (id int, dates varchar(12), orderid int)
INSERT INTO #test
SELECT 1, '2013/7/10', 1 UNION all
SELECT 2, '2013/7/11', 1 UNION all
SELECT 3, '2013/7/12', 1 UNION all
SELECT 4, '2013/7/10', 2 UNION all
SELECT 5, '2013/7/11', 3 UNION all
SELECT 6, '2013/7/10', 4 UNION all
SELECT 7, '2013/7/11', 4
SELECT orderid FROM #test GROUP BY orderid HAVING COUNT(dates)=DATEDIFF(day,'2013-7-10','2013-7-12') + 1
DROP TABLE #test
CREATE TABLE #test (id int, dates varchar(12), orderid int)
INSERT INTO #test
SELECT 1, '2013/7/10', 1 UNION all
SELECT 2, '2013/7/11', 1 UNION all
SELECT 3, '2013/7/12', 1 UNION all
SELECT 4, '2013/7/10', 2 UNION all
SELECT 5, '2013/7/11', 3 UNION all
SELECT 6, '2013/7/10', 4 UNION all
SELECT 7, '2013/7/11', 4
SELECT orderid FROM #test WHERE dates>='2013/7/10' AND dates<='2013/7/12' GROUP BY orderid HAVING COUNT(dates)=DATEDIFF(day,'2013-7-10','2013-7-12') + 1
DROP TABLE #test加个WHERE dates>='2013/7/10' AND dates<='2013/7/12'
if object_id('tempdb..#test')>0
DROP TABLE #testCREATE TABLE #test (id int, dates varchar(12), orderid int)
INSERT INTO #test
SELECT 1, '2013/7/10', 1 UNION all
SELECT 2, '2013/7/11', 1 UNION all
SELECT 3, '2013/7/12', 1 UNION all
SELECT 4, '2013/7/10', 2 UNION all
SELECT 5, '2013/7/11', 3 UNION all
SELECT 6, '2013/7/10', 4 UNION all
SELECT 7, '2013/7/11', 4 UNION all
SELECT 8, '2013/7/11', 4 UNION all
SELECT 9, '2013/7/10', 5 UNION all
SELECT 10, '2013/7/11', 5 UNION all
SELECT 11, '2013/7/11', 5 UNION all
SELECT 10, '2013/7/12', 5 select orderid from
(
SELECT orderid,dates FROM #test a where dates in('2013/7/10','2013/7/11','2013/7/12')
GROUP BY orderid,dates
) a GROUP BY orderid having count(*)=3/*
1
5
*/
select * from tab_name where orderid in
(select orderid from tab_name
group by orderid
having count(date)>=3)
orderid为5的,日期有重重的,也是符合条件,也是会出来.
最终的目的,是保证2013/7/10至2013/7/12之间日期都要有(每一天至少要有一笔)。