110,570
社区成员
发帖
与我相关
我的任务
分享
select convert(varchar(4),[t1.MakeTime],120) as Y,
count(t1.N) as MC ,
count(t2.N) as RC
from MakeRecord t1, RepairRecord t2
where convert(varchar(4),[t1.MakeTime],120) = convert(varchar(4),[t2.StartTime],120)
group by convert(varchar(4),[t1.MakeTime],120)
order by Y desc;
CREATE TABLE Make(D VARCHAR(20), N VARCHAR(10), Q VARCHAR(10))
CREATE TABLE Repair(D VARCHAR(20), N VARCHAR(10), B VARCHAR(10))
INSERT dbo.Make
SELECT '2000-01-01', '001', 'False' UNION ALL
SELECT '2000-01-02', '002', 'True' UNION ALL
SELECT '2001-01-01', '003', 'True';
INSERT dbo.Repair
SELECT '2000-01-30', '001', 'False' UNION ALL
SELECT '2001-02-01', '003', 'True';
SELECT a.*,b.RC FROM
(SELECT YEAR(D) AS [D],COUNT(N) AS [MC] FROM dbo.Make GROUP BY YEAR(D))a
LEFT JOIN
(SELECT YEAR(D) AS [D],COUNT(N) AS [RC] FROM dbo.Repair GROUP BY YEAR(D))b
ON a.D = b.D
DROP TABLE dbo.Make,dbo.Repair
/*
D MC RC
----------- ----------- -----------
2000 2 1
2001 1 1
(2 個資料列受到影響)
*/
select A.Y,A.MC,B.RC from (select year(MakeTime)as Y,count(*)as MC from MakeRecord group by year(MakeTime)) A,(select year(StartTime)as Y,count(*)as RC from RepairRecord group by year(StartTime)) B where A.Y=B.Y