按照CategoryID不同,分别把4条数据读出
解决方案 »
- 一个DATEADD中的问题!!
- 哪个方法速度好、性能好,可维护性好?
- 关于索引扫描的问题?
- SQL2000删除重复记录的问题
- /3g /pae awe 我这样理解对不对???
- 昨天还好好的。今天数据库怎么都连不上,高手过来看看。
- log,mdf文件都在,但是无法附加.sql2000.
- 求一条很难的sql语句,谢谢!
- LogExplorer可不可以知道是哪个WINDOWS用户执行了DROP TABLE操作??
- 恭喜诸位新年发才(zhuzhichao(竹之草) flowerofwind(现实很残酷)IronPromises(铁诺)...请进)
- 请大家进来讨论一下~~~~~~~~有关两个表更新的问题
- SQLServer查询语句总结
where d.PubdepartmentID = A.PubdepartmentID
order by A.CategoryID不知道你要的是不是这样的情况!
.......
from A
join Department on A.PubDepartmentID=B.ID
.......
from A
right join Department on A.PubDepartmentID=B.ID
--创建函数
create function getString(@Did varchar(10))--串联一个部门所有的CategoryID
returns varchar(400)
as
begin
declare @str varchar(400)
set @str=''
select @str=@str+'/'+CategoryID from A where A.PubDepartmentID= @Did
set @str=stuff(@str, 1, 1, '')
return @str
endselect PubDepartmentID,[新名称]=dbo.getString(PubDepartmentID)
from A
group by PubDepartmentID
--清除查询出来类似数据
PubDepartmentID 新名称
1 1/2/3
2 2/3
3 4
4 1/2/3/4
表A部分结构,有字段ID,Content,CategoryID,PubdepartmentID,然后其中PubdepartmentID可以和Department表中的ID相关联,现在表A中存在4条数据,他们的CategoryID分别是1,2,3,4。我想用一条SQL把这些数据一次从A表中读出来,变成一条记录,同时把CategoryID=1的那条记录的Content用新字段名Content1来表示,把CategoryID=2记录的Content用新字段名Content2来表示.....依次类推..
不知道这次说明白没有,一会结贴,分不够我在加昂
我的SQL如下:
select b.* ,
case a.CategoryID when 1 then a.Content else '' End AS Content1,
case a.CategoryID when 2 then a.Content else '' End AS Content2,
case a.CategoryID when 3 then a.Content else '' End AS Content3,
case a.CategoryID when 4 then a.Content else '' End AS Content4
from department b left join A on b.ID = a.PubdepartmentID不知道我的理解对否?
elect b.ID,
(select Content from a where a.PubdepartmentID = b.id and a.CategoryID = 1) as Content1,
(select Content from a where a.PubdepartmentID = b.id and a.CategoryID = 2) as Content2,
(select Content from a where a.PubdepartmentID = b.id and a.CategoryID = 3) as Content3,
(select Content from a where a.PubdepartmentID = b.id and a.CategoryID = 4) as Content4
from department b如果CategoryID不知道到底有多少笔,可能n笔,1,2,3,4,5,6...n,那么还要这样可能要用到动态@sql
不过就不是超级简单
创建函数:
create function getString(@id int)
returns varchar(400)
as
begin
declare @str varchar(400)
select @str=content from 表A where CategoryID=@id
return @str
end
查询语句:
select top 1 1 as ID,getString(1) as content1,getString(2) as content2,getString(3) as content3,getString(4) as content4结果:
ID content1 content2 content3 content4
1 c1 c2 c3 c4
好象只有一个表的操作,不知道楼主提Department表是何用意?
---楼主是可能这样
Department表:
ID D_NAM
001 人事部
002 总务部
003 船务部
004 总经理室
...A表:
PubdepartmentID CategoryID content
001 1 人力资源管理 --我自己随便编的
001 2 教育训练
002 3 庶务
002 4 XXX--结果
PubdepartmentID content1 content2 content3 content4
001 人力资源管理 教育训练
002 庶务 XXX
003 --没有就是空白罗
004
...
的不对,我刚开始以为对!以为我的数据:
他的语句选出来得不是和在一笔,而是成个一个斜对角线,我前面说的倒是可以,我现在把他改写为
动态抓取,因为CategoryID可能不确定,或许有多笔,用动态sql就没有这样的顾虑
D_id Content1 Content2 Content3 Content4 Content5
----- ---------- ---------- ---------- ---------- ----------
QC aa
QC bb
QC cc
QC dd
QC ee
PMIC ff
PMIC gg
PMIC hh
PMIC ii
MIS (10 row(s) affected)////////////////
测试数据
create table #Department(D_id varchar(5),D_nam varchar(20))
create table #A(PubdepartmentID varchar(5),CategoryID int,Content varchar(10))
insert into #Department
select 'QC','品管部' union all
select 'PMIC','績效管理中心' union all
select 'MIS','資訊部'insert into #A
select 'QC',1,'aa' union all
select 'QC',2,'bb' union all
select 'QC',3,'cc' union all
select 'QC',4,'dd' union all
select 'QC',5,'ee' union all
select 'PMIC',1,'ff' union all
select 'PMIC',2,'gg' union all
select 'PMIC',3,'hh' union all
select 'PMIC',5,'ii'declare @str as varchar(6000) --用来存取动态sql
set @str = ''
select distinct CategoryID into #S from #A --抓取多少个不同的CategoryID存入临时表#Sselect @str = @str + ',Content'+ convert(varchar(2),CategoryID) + '=isnull((select Content from #A where #A.PubdepartmentID = #Department.D_ID and #A.CategoryID = ' + convert(varchar(2),CategoryID) + '),'''')'
from #S
set @str = stuff(@str,1,1,'')
set @str = 'select #Department.D_ID,' + @str + ' from #Department'
--print @str
exec(@str)
/*
(5 row(s) affected)D_ID Content1 Content2 Content3 Content4 Content5
----- ---------- ---------- ---------- ---------- ----------
QC aa bb cc dd ee
PMIC ff gg hh ii
MIS
*/难倒不是太难,但是超级简单说不上!拼接字串也是很磨人的