用语句
select a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id
得到记录如下
sn process_id data_id measuredata
12770006 1 1 1.34
12770006 1 2 0.46
12770006 1 3 9.82
14061916 2 1 5.5
14061916 2 2 4.36
12770006 2 1 6.43
12770006 2 2 0.12
12770006 2 3 6.73
14061916 3 1 3.4
14061916 3 2 1.1但是这不符合客户的要求,他们的要求是
sn process_id field1 field2 field3 field4
12770006 1 1.34 0.46 9.82 Null
14061916 2 5.5 4.36 Null Null
12770006 2 6.43 0.12 6.73 Null
14061916 3 3.4 1.1 Null Null就是在原来查询记录的基础上,把measuredata按照data_id的顺序横向排列,但是需要注意的是不同的sn可能measuredata的数目不同.如12770006有3个,14061916只有2个,因此在field3,fiels4处置为Null.但最长到field4.不知我表达清楚没有,如能解决高分相送,分有的是---------关键是项目催的紧!!!!请给出具体的sql语句,谢谢!
select a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id
得到记录如下
sn process_id data_id measuredata
12770006 1 1 1.34
12770006 1 2 0.46
12770006 1 3 9.82
14061916 2 1 5.5
14061916 2 2 4.36
12770006 2 1 6.43
12770006 2 2 0.12
12770006 2 3 6.73
14061916 3 1 3.4
14061916 3 2 1.1但是这不符合客户的要求,他们的要求是
sn process_id field1 field2 field3 field4
12770006 1 1.34 0.46 9.82 Null
14061916 2 5.5 4.36 Null Null
12770006 2 6.43 0.12 6.73 Null
14061916 3 3.4 1.1 Null Null就是在原来查询记录的基础上,把measuredata按照data_id的顺序横向排列,但是需要注意的是不同的sn可能measuredata的数目不同.如12770006有3个,14061916只有2个,因此在field3,fiels4处置为Null.但最长到field4.不知我表达清楚没有,如能解决高分相送,分有的是---------关键是项目催的紧!!!!请给出具体的sql语句,谢谢!
解决方案 »
- SET ROWCOUNT 这个语句是怎么用滴
- 2个表查询加where User.GroupID=3查的怎么还是所有的数据
- 关于表变量的问题
- 只剩最后10分了,请大家不要嫌弃,帮我看看这个触发器调用存储过程的问题,快疯了
- 如何跳过出错的SQL语句段?
- sql server 的execute,sp_executesql方法如何从动态select语句中取得返回值???
- 再请教一遍数据库同步,邹老大帮我看看:100台以上的服务器将数据发布到一台中心服务器现实么?
- 如何实现备份服务器,
- 如何知道SQL SERVER7.0中数据库的每张表的最近更新时间!
- 请大家帮,帮,帮,帮个忙!!!
- 帮忙写一个Sql语句好吗?谢谢!急急急!
- 这条查询语句有问题,只能显示500条以内的数据(不管新的还是旧的)超过500后新的数据就不显示了, 帮忙看一下 多谢!!
As
(
Select Distinct Course From table1
)Select @cols = IsNull(@cols + ',[','[') + Course + ']' From CTECourseDECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT [Name], [Course], [Grade]
FROM table1) as Header
PIVOT(SUM([Grade]) FOR [Course] IN(' + @cols + N')) AS Piv'
PRINT @sql -- for debugging
EXEC sp_executesql @sql
[field1]=max(case when b.data_id=1 then b.measuredata end),
[field2]=max(case when b.data_id=2 then b.measuredata end),
[field3]=max(case when b.data_id=3 then b.measuredata end),
[field4]=max(case when b.data_id=4 then b.measuredata end)
From cr_test a
left join cr_data b on a.test_id = b.test_id
Group By a.SN,a.Process_id,
order by a.sn,b.data_id
Group By a.SN,a.Process_id,
order by a.sn,b.data_id
---------------------------
Group By a.SN,a.Process_id
order by a.SN,a.Process_id
set @s='select ta.sn,ta.process_id'
select @s=@s+',field'+ltrim(tb.data_id)+'=sum(case when tb.data_id='''+ltrim(tb.data_id)+''' then tb.measuredata else 0 end)'
from (select top 100 percent a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id) tb
group by tb.data_id
set @s=@s+' from (select a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id) ta group by ta.sn,ta.process_id'
exec(@s)
这个语句其实只是一个示例,其中还牵扯到很多表.因此我打算不改变原来的语句.而在原来语句查询结果的基础上再进行一次处理请问应该怎么解决???
CREATE TABLE tb(sn varchar(20),process_id int,data_id int,measuredata numeric(9,2))
INSERT tb SELECT '12770006',1,1,1.34
UNION ALL SELECT '12770006',1,2,046
UNION ALL SELECT '12770006',1,3,9.82
UNION ALL SELECT '14061916',2,1,5.5
UNION ALL SELECT '14061916',2,2,4.36
UNION ALL SELECT '12770006',2,1,6.43
UNION ALL SELECT '12770006',2,2,0.12
UNION ALL SELECT '12770006',2,3,6.37
UNION ALL SELECT '14061916',3,1,3.4
UNION ALL SELECT '14061916',3,2,1.1--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT sn,process_id'
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+','+QUOTENAME(data_id)
+N'=SUM(CASE data_id WHEN '+QUOTENAME(data_id,N'''')
+N' THEN measuredata END)'
FROM tb
GROUP BY data_id--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY sn,process_id')