目录

关键词：SqlServer

问题背景

1. hql语句不熟悉，多表联查的时候都是在业务层在写其他的方法去查。（感觉真的好菜啊，但是还是要直面现实）。
2. 测试的时候发现数据过滤的不对。有很多所谓的”重复"数据。以我项目中的两个场景为例。

场景1

如图化学品和类型是一对多的关系。如果按普通的左关联查询查到的数据应该是，每一个化学品有几个类型就会有几条数据。所以这里用到的是==多行转多列==

--多行转多列

SELECT O.CHEMICAL_ID,O.CAS,O.JC,

==max(CASE O.DETAILS_NAME WHEN '重点监管' THEN O.TZLBBM ELSE 0 end)重点监管,

max(CASE O.DETAILS_NAME WHEN '易制毒' THEN O.TZLBBM ELSE 0 end)易制毒,

max(CASE O.DETAILS_NAME WHEN '易制爆' THEN O.TZLBBM ELSE 0 end)易制爆,

max(CASE O.DETAILS_NAME WHEN '剧毒化学品' THEN O.TZLBBM ELSE 0 end)剧毒化学品,

max(CASE O.DETAILS_NAME WHEN '高毒物品' THEN O.TZLBBM ELSE 0 end)高毒物品==

FROM (SELECT C.*,E.DETAILS_NAME,T.TZLBBM FROM TB_BASE_CHEMICAL AS C

LEFT JOIN TB_BASE_CHEMICAL_TZLB AS T ON T.CHEMICAL_ID=C.CHEMICAL_ID

LEFT JOIN TB_HSE_DICTIONARY_DETAILS AS E ON E.DETAILS_CODE=T.TZLBBM AND E.COM_DEPTCODE='20000000')AS O

GROUP BY O.CHEMICAL_ID,O.CAS,O.JC

效果如图

场景2

这里的化学品和类别也是一对多的关系，用到的多行转一列。

用到了 stuff(select ',' + fieldname from tablename for xml path('')),1,1,'') 函数

这一整句的作用是将多行fieldname字段的内容串联起来，用逗号分隔。

SELECT

qywhpglid ,typeName = ( STUFF(( SELECT ',' + typeName

FROM (

SELECT

a.QYWHPGL_ID AS qywhpglid,

g.DETAILS_NAME AS typeName

FROM

TB_HSE_FOUN_QYWHPGL a

INNER JOIN TB_BASE_CHEMICAL c ON a.WHPBM = c.CHECODE

INNER JOIN TB_BASE_CHEMICAL_TZLB f ON f.CHEMICAL_ID = c.CHEMICAL_ID

INNER JOIN TB_HSE_DICTIONARY_DETAILS g ON f.TZLBBM = g.DETAILS_CODE and g.ORGCODE='20000000'

WHERE

a.U_VALIDATE = '1'

)AS R

WHERE ==R.qywhpglid = Test.qywhpglid==

FOR XML PATH('')), 1, 1, '') )

FROM

(

SELECT

a.QYWHPGL_ID AS qywhpglid,

g.DETAILS_NAME AS typeName

FROM

TB_HSE_FOUN_QYWHPGL a

INNER JOIN TB_BASE_CHEMICAL c ON a.WHPBM = c.CHECODE

INNER JOIN TB_BASE_CHEMICAL_TZLB f ON f.CHEMICAL_ID = c.CHEMICAL_ID

INNER JOIN TB_HSE_DICTIONARY_DETAILS g ON f.TZLBBM = g.DETAILS_CODE and g.ORGCODE='20000000'

WHERE

a.U_VALIDATE = '1'

) AS Test

GROUP BY qywhpglid

注意：斜体加粗部分是两个表，它们的主键和要合并的行是一样的。如果不加上主键相等（标黄部分就会出现重复数据）