Sqlserver复杂查询

--联表修改
update xyzrb
set xyzrb.xy_card=tablsb.card
from xyzrb left join  tablsb on xyzrb.xybh=tablsb.xybh

--取重复数据的各最大一条
select x.comment as comment,x.fidcoachid as fidcoachid from xcb_comment x 
right join 
(select  max(id) as id,fidcoachid from xcb_comment  group by  fidcoachid) as mc 
on mc.id=x.id;

--查询出多行数据合并成一行
SELECT  s.*, B.name AS fidDModelName, 
subjects = (stuff((SELECT ‘,‘ + h.name FROM  xcb_coachattr t LEFT JOIN hrm_BaseData h ON h.id = t.fiddata AND t .code = ‘subjects‘ WHERE  t .fidcoachid = s.id FOR xml path(‘‘)), 1, 1, ‘‘)), 
regions = (stuff((SELECT ‘,‘ + h.name FROM  xcb_coachattr t LEFT JOIN hrm_BaseData h ON h.id = t.fiddata AND t .code = ‘Region‘ WHERE     t .fidcoachid = s.id FOR xml path(‘‘)), 1, 1, ‘‘))
FROM  xcb_coach s LEFT JOIN hrm_BaseData B ON S.fidDModel = B.id AND B.Code = ‘DModel‘ WHERE 1 = 1

--高级多表联表查询
select
s.chrstuno as schrstuno,
s.chrname as schrname,
s.chrphone as schrphone,
B.name as fidDModelName,
c.chrname as cchrname,
c.chrphone as cchrphone,
c.carNo as ccarNo,
B1.name as fidsubjectname,
B2.name as fidRegionname,
CONVERT(varchar(10),o.orderdate,23) as orderdate,
case when o.paytype =1 then ‘现金支付‘
when o.paytype = 2 then ‘课时支付‘
end as paytype,
from xcb_order o
left join hrm_BaseData B on o.fidDModel=B.id and B.Code=‘DModel‘ 
left join hrm_BaseData B1 on o.fidsubject=B1.id and B1.Code=‘subjects‘ 
left join hrm_BaseData B2 on o.fidRegion=B2.id and B2.Code=‘Region‘ 
left join xcb_students s on o.fidstudent=s.id
left join xcb_coach c on o.fidcoachid=c.id 
where o.fidcorp=1 and o.statue in(1,2,3,4,5,6) 

--生成Java实体对象
SELECT 
case 
when isnullable=‘0‘ then ‘@NotNull‘+CHAR(10)+CHAR(13)
else ‘‘
end+
case
when xtype=‘35‘ or xtype=‘231‘ or xtype=‘239‘ or xtype=‘175‘ or xtype=‘167‘ 
then ‘@Size(max = ‘+convert(varchar(12),length)+‘)‘+CHAR(13)+CHAR(10)+‘@Column(name = "‘+Name+‘")‘+CHAR(13)+CHAR(10)+‘private String ‘
when xtype=‘48‘ or xtype=‘52‘  or xtype=‘56‘ 
then ‘@Column(name = "‘+Name+‘")‘+CHAR(13)+CHAR(10)+‘private int ‘
when xtype=‘106‘ or xtype=‘108‘  
then ‘@Column(name = "‘+Name+‘")‘+CHAR(13)+CHAR(10)+‘private BigDecimal ‘
when xtype=‘61‘ or xtype=‘189‘ 
then ‘@Column(name = "‘+Name+‘")‘+CHAR(13)+CHAR(10)+‘@Temporal(TemporalType.TIMESTAMP)‘+CHAR(13)+CHAR(10)+‘private Date ‘
end+Name+‘;‘,xtype FROM SysColumns WHERE id=Object_Id(‘xcb_clglb‘) 

--int类型字段拼接字符转换
select convert(varchar(30),id)+‘55‘ from xcb_students where id=11;

--查询所有表名
select ‘select *  from ‘+ name+‘;  --‘ from sysobjects where xtype=‘u‘ order by name;

--查询表所有字段
select name+‘,‘ FROM SysColumns WHERE id=Object_Id(‘xyfyb‘) ;