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') ;

--查看表字段及说明
SELECT t.[name] AS 表名,c.[name] AS 字段名,cast(ep.[value] as varchar(100)) AS [字段说明]
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties AS ep 
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE t.name='TableName'

--快速查询表数据结构  
SELECT CASE WHEN col.colorder = 1 THEN obj.name ELSE '' END AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep.[value], '') AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'  ELSE ''  END AS 标识 ,
CASE WHEN EXISTS ( SELECT   1  FROM     dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id  AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'  WHERE    sc.id = col.id  AND sc.colid = col.colid ) THEN '√'  ELSE ''  END AS 主键 ,  
CASE WHEN col.isnullable = 1 THEN '√'  ELSE ''  END AS 允许空 , 
ISNULL(comm.text, '') AS 默认值 FROM    dbo.syscolumns col
LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id  AND obj.xtype = 'U' AND obj.status >= 0
LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id  AND col.colid = ep.minor_id  AND ep.name = 'MS_Description'
LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id  AND epTwo.minor_id = 0  AND epTwo.name = 'MS_Description' WHERE obj.name = 'cartab'--表名
ORDER BY col.colorder ;

 

--生成Java实体类（含字段说明）
SELECT
case 
when c.is_nullable='0' then '@NotNull'
else ''
end+
case
when c.user_type_id='35' or c.user_type_id='231' or c.user_type_id='239' or c.user_type_id='175' or c.user_type_id='167' 
then '@Size(max = '+convert(varchar(12),c.max_length)+')'+'@Column(name = "'+c.name+'")'+'private String '
when c.user_type_id='48' or c.user_type_id='52'  or c.user_type_id='56' 
then '@Column(name = "'+c.name+'")'+'private int '
when c.user_type_id='106' or c.user_type_id='108'  
then '@Column(name = "'+c.name+'")'+'private BigDecimal '
when c.user_type_id='61' or c.user_type_id='189' 
then '@Column(name = "'+c.name+'")'+'@Temporal(TemporalType.TIMESTAMP)'+'private Date '
end+c.name+';//'+cast(ISNULL(ep.[value],'') as varchar(100)) FROM  sys.tables AS t
INNER JOIN sys.columns  AS c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties AS ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id 
WHERE t.name='basedata';