sql

①待领样（分多种情况，case when 里复杂）
SELECT CASE
WHEN T6.groupcount > 1 THEN
'1'
ELSE
'0'
END ifothers

FROM LIMS_folderservgrp T
LEFT JOIN LIMS_folders T2
ON T.foldersid = T2.foldersid
AND T2.PAUSEMARK = '0'
LEFT JOIN LIMS_CLEARANCEFORM T3
ON T2.CLEARANCEID = T3.CLEARANCEID
LEFT JOIN LIMS_PRODGROUP T5
ON T5.PRODGROUPID = T3.Inspectionitem
LEFT JOIN base_corp T4
ON T.dept = T4.corpcode
LEFT JOIN (SELECT COUNT(*) groupcount, foldersid
FROM LIMS_FOLDERSERVGRP
GROUP BY foldersid) T6
ON T6.foldersid = T.foldersid
LEFT JOIN LIMS_FOLDERSTIMELIMIT T8
ON T.Foldersid = T8.FOLDERSID
AND T8.Dept = T.Dept
AND T8.OPERATIONNO = '2'
LEFT JOIN LIMS_DOMAINREPORT LD
ON LD.REQSUBTYPEID = t3.REQSUBTYPID
AND LD.dept = T3.Dept
WHERE EXISTS (SELECT 1
FROM right_rightuser r
WHERE classficationid = '1'
AND r.usercode = 'nizhengfei'
AND T.dept = r.datacode)-- 存在登陆人的科室，若fs表没有登陆人的科室，根本没关系不显示。
AND EXISTS (SELECT 1
FROM lims_ordtask ot
WHERE ot.foldersid = t2.foldersid
AND ot.wfnodecode = 'RWFP'
AND T.dept = ot.DEPT)-- 必须是在任务分配节点的
AND CASE
WHEN T.DEPT != 'WSWS' AND EXISTS
(SELECT 1
FROM lims_folderservgrp fs
WHERE T.foldersid = fs.foldersid HAVING COUNT(1) > 1)
THEN
EXISTS (SELECT 1
FROM lims_folderservgrp fs
WHERE T.foldersid = fs.foldersid
AND fs.DEPT = 'WSWS'
AND fs.available != '0')
AND EXISTS
(SELECT 1
FROM lims_folderservgrp fs
WHERE T.foldersid = fs.foldersid
AND fs.DEPT != 'WSWS'
AND fs.available = '0')
ELSE
T.available = '0'-- 展示两种条件
-- 1.当fs这条数据的科室是理化室，并且fs表存在两个科室的时候
-- 1.条件是exists（微生物并且已经领样）and exists（理化室并且未领样）这样fs的这条数据会展示出来
-- 2.当fs这条数据只有微生物室，或者微生物的这条数据有两个科室，或者这条理化室数据只有一个
-- 2.and T.available = '0'，fs表，未领样。就展示这条数据
-- 总的来说有一下情况，微生物（只有微生物 T.available = '0'，有两个 T.available = '0'）、
-- 理化室（只有T.available = '0'，有两个比较复杂）
END
AND T.auditflag = '0'-- 未通过审核
AND ld.dept = 'WXIFC'
AND (T2.STATUS <> '终止检验' OR T2.STATUS IS NULL)
ORDER BY t3.ACCEPTDATE DESC

 

②按照省份分类，然后统计个数

select a.leibie, count(*)
from (select case
when AUTHORIZATIONUNIT like '%南京%' then
'南京'

when AUTHORIZATIONUNIT not like '%南京%' and AUTHORIZATIONUNIT like '%江苏%' then
'省内'
when AUTHORIZATIONUNIT not like '%南京%'and AUTHORIZATIONUNIT not like '%江苏%'
then
'省外'
else
AUTHORIZATIONUNIT
end leibie

from lims_clearanceform c
where AUTHORIZATIONUNIT is not null
) a
group by a.leibie

③case when   和sum结合

sum(case when c.sampletype = '体外诊断测试剂' and c.acceptancetype='/' then
1
else
0
end) 试剂注册,
sum(case when c.sampletype = '无源' and c.acceptancetype='/' then
1
else
0
end) 无源注册,

 

 

④,group_concat(     和vm _concat

 select id,group_concat(name) from aa group by id;    mysql