--联表修改 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';