非常感谢所有帮助过我的朋友, 谢谢你们, 本人是一个C#程序员, 经常用到数据库, 但是数据库又不行, 所以经常来这提问, 每次提问都能得到满意的答案,
谢谢你们!希望这个论坛越来越火. (虽然已经很火了)
现在又有一个问题了
有个table, 有3个columns,分别是LogLength, CutLength 和 date
现在要做一个查询 SELECT COUNT(CutLength - LogLength) AS CountNumber, CutLength - LogLength AS NumberName, CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)在这个结果里头加个条件
Cutlength - LogLength 如果 大等于15, 就都归于15 Cutlength - LogLength 如果 小等于-15 都归于-15,
结果类似于:CountNumber NumberName CutDate
120 15 1-4-2009
100 12 1-4-2009
77 9 1-4-2009
100 5 1-4-2009
120 1 1-4-2009
120 -5 1-4-2009
120 -7 1-4-2009
120 -9 1-4-2009
60 -11 1-4-2009
90 -13 1-4-2009
88 -15 1-4-2009
谢谢大家,祝大家开心
谢谢你们!希望这个论坛越来越火. (虽然已经很火了)
现在又有一个问题了
有个table, 有3个columns,分别是LogLength, CutLength 和 date
现在要做一个查询 SELECT COUNT(CutLength - LogLength) AS CountNumber, CutLength - LogLength AS NumberName, CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)在这个结果里头加个条件
Cutlength - LogLength 如果 大等于15, 就都归于15 Cutlength - LogLength 如果 小等于-15 都归于-15,
结果类似于:CountNumber NumberName CutDate
120 15 1-4-2009
100 12 1-4-2009
77 9 1-4-2009
100 5 1-4-2009
120 1 1-4-2009
120 -5 1-4-2009
120 -7 1-4-2009
120 -9 1-4-2009
60 -11 1-4-2009
90 -13 1-4-2009
88 -15 1-4-2009
谢谢大家,祝大家开心
select T.CountNumber,case when T.NumberName>15 then T.NumberName=15 when T.NumberName<-15 then T.NumberName=15 else T.NumberName end,T.CutDate from
(SELECT COUNT(CutLength - LogLength) AS CountNumber,(CutLength - LogLength) then AS NumberName, CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)) T
select T.CountNumber,case when T.NumberName>15 then T.NumberName=15 when T.NumberName<-15 then T.NumberName=15 else T.NumberName end,T.CutDate from
(SELECT COUNT(CutLength - LogLength) AS CountNumber,(CutLength - LogLength) then AS NumberName, CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
CASE WHEN CutLength - LogLength >15 THEN CutLength - LogLength THEN 15 ELSE -15 END AS NumberName,
CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)
SELECT COUNT(CutLength - LogLength) AS CountNumber,
CASE WHEN CutLength - LogLength >15 THEN CutLength - LogLength THEN CutLength - LogLength ELSE -(CutLength - LogLength) END AS NumberName,
CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)两个选一个.
CASE WHEN CutLength - LogLength >15 THEN 15 ELSE -15 END AS NumberName,
CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)4楼第一个改一下.
CASE WHEN CutLength - LogLength >15 THEN 15 ELSE -15 END AS NumberName,
CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CASE WHEN CutLength - LogLength >15 THEN 15 ELSE -15 END AS NumberName, CONVERT(varchar(10), Date, 120)
when 你的條件 then 你的結果
when 你的條件 then 你的結果
....
else 條件之外 你的結果 end
NumberName=(CASE WHEN CutLength - LogLength >15 THEN 15
WHEN CutLength - LogLength <-15 THEN -15
ELSE (CutLength - LogLength) END),
CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)
CASE WHEN CutLength - LogLength >=15 THEN 15
WHEN CutLength - LogLength <=-15 THEN -15
ELSE CutLength - LogLength
END AS NumberName,
CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)重新回来看下题目,发现跟要求还有偏差.最后修正.
2搂的错误是
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.5搂运行没错, 但是结果是错的
你这样只有15 和 -15 两种, 介于中间的数据都没有了
CASE WHEN CutLength - LogLength >=15 THEN 15
WHEN CutLength - LogLength <=-15 THEN -15
ELSE CutLength - LogLength
END AS NumberName,
CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)
这样可以了不?
1 -15
1 -15
1 -15
1 -15
1 -15
2 -15
1 -15
2 -15
1 -15
1 -15
2 -15
1 -15
2 -15
1 -15
1 -15
1 -15
1 -15
3 -15
2 -15
2 -15
2 -15
1 -14
1 -13
3 -12
1 -11
1 -10
1 -9
2 -8
7 -7
10 -6
30 -5
59 -4
137 -3
177 -2
276 -1
383 0
437 1
420 2
401 3
287 4
227 5
157 6
103 7
84 8
47 9
34 10
26 11
34 12
19 13
10 14
22 15
16 15
1 15
12 15
4 15
40 15
6 15
1 15
1 15
3 15
22 15
27 15
4 15
3 15
28 15
8 15
10 15
22 15
22 15
5 15
13 15
6 15
7 15
17 15
1 15
40 15
2 15
25 15
6 15
2 15
11 15
27 15
10 15
4 15
25 15
16 15
11 15
1 15
21 15
12 15
9 15
8 15
1 15
1 15
31 15
14 15
8 15
9 15
2 15
6 15
4 15
22 15
1 15
10 15
7 15
2 15
3 15
1 15
5 15
25 15
3 15
1 15
很多15和-15的没有GROUP 在一起
select T.CountNumber,(case when T.NumberName>15 then
15 when T.NumberName<-15 then -15 else T.NumberName end) 'T.NumberName',T.CutDate from
(SELECT COUNT(CutLength - LogLength) AS CountNumber,(CutLength - LogLength) AS NumberName, CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CutLength - LogLength, CONVERT(varchar(10), Date, 120)) T
SELECT COUNT(CutLength - LogLength) AS CountNumber,
CASE WHEN CutLength - LogLength >=15 THEN 15
WHEN CutLength - LogLength <=-15 THEN -15
ELSE CutLength - LogLength
END AS NumberName,
CONVERT(datetime, CONVERT(varchar(10), Date, 120)) AS CutDate
FROM dbo.PLCData
GROUP BY CASE WHEN CutLength - LogLength >=15 THEN 15
WHEN CutLength - LogLength <=-15 THEN -15
ELSE CutLength - LogLength
END, CONVERT(varchar(10), Date, 120)这样就OK了.
select Sum(countNumber), NumberName
From
(SELECT COUNT(CutLength - LogLength) AS CountNumber,
CASE WHEN CutLength - LogLength >=15 THEN 15
WHEN CutLength - LogLength <=-15 THEN -15
ELSE CutLength - LogLength
END AS NumberName
FROM dbo.PLCData
GROUP BY CutLength - LogLength) as TGroup by NumberName
From
(SELECT COUNT(CutLength - LogLength) AS CountNumber,
(CASE WHEN CutLength - LogLength >=15 THEN 15
WHEN CutLength - LogLength <=-15 THEN -15
ELSE CutLength - LogLength
END) AS NumberName
FROM dbo.PLCData
GROUP BY CutLength - LogLength) as T
Group by NumberName
可以
可以了~!~
数据库开发!