SELECT
AirLineCode,
AirlineName,
FullName,
SUM(DISTINCT INDIACount) AS INDIACount,
SUM(DISTINCT MEMOCount) AS MEMOCount,
SUM(DISTINCT ASIACount) AS ASIACount,
SUM(DISTINCT LATINCount) AS LATINCount,
SUM(DISTINCT ATLANTICCount) AS ATLANTICCount,
SUM(DISTINCT PACIFICCount) AS PACIFICCount,
SUM(DISTINCT DOMESTICCount) AS DOMESTICCount FROM
(
SELECT DISTINCT
SF.AirLineCode, A.AirlineName, '(' + A.AirLineCode + ') ' + A.AirlineName AS FullName,
ISNULL(INDIA, 0)INDIACount,
ISNULL(MEMO, 0)MEMOCount,
ISNULL(ASIA, 0)ASIACount,
ISNULL(LATIN, 0)LATINCount,
ISNULL(ATLANTIC, 0)ATLANTICCount,
ISNULL(PACIFIC, 0)PACIFICCount,
ISNULL(DOMESTIC, 0)DOMESTICCount,
CASE WHEN
(
ISNULL(INDIA, 0)
+ ISNULL(MEMO, 0)
+ ISNULL(ASIA, 0)
+ ISNULL(LATIN, 0)
+ ISNULL(ATLANTIC, 0)
+ ISNULL(PACIFIC, 0)
+ ISNULL(DOMESTIC, 0)
) =0 THEN 'T' else 'F' END Show
FROM T_FareSheet SF
INNER JOIN MST_Airlines A
ON SF.AirLineCode = A.AirLineCode
LEFT JOIN (
SELECT COUNT(*) INDIA, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%INDIA' + '%'
PATINDEX('%NDIA%',MARKET) >0
GROUP BY AirLineCode
)TableINDIA ON TableINDIA.AirLineCode like '%' + SF.AirLineCode + '%'
LEFT JOIN(
SELECT COUNT(*) MEMO, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%MEMO'+ '%'
PATINDEX('%MEMO%',MARKET) >0
GROUP BY AirLineCode
) TableMEMO ON TableMEMO.AirLineCode like '%' + SF.AirLineCode + '%'
LEFT JOIN(
SELECT COUNT(*) ASIA, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%ASIA' + '%'
PATINDEX('%ASIA%',MARKET) >0
GROUP BY AirLineCode
) TableASIA ON TableASIA.AirLineCode like '%' + SF.AirLineCode + '%'
LEFT JOIN(
SELECT COUNT(*) LATIN, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%LATIN' + '%'
PATINDEX('%LATIN%',MARKET) >0
GROUP BY AirLineCode
) TableLATIN ON TableLATIN.AirLineCode like '%' + SF.AirLineCode + '%'
LEFT JOIN(
SELECT COUNT(*) ATLANTIC, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%EUROPE' + '%'
PATINDEX('%EUROPE%',MARKET) >0
GROUP BY AirLineCode
) TableATLANTIC ON
TableATLANTIC.AirLineCode like '%' + SF.AirLineCode + '%'
--PATINDEX('%' + SF.AirLineCode + '%',TableATLANTIC.AirLineCode) >0
LEFT JOIN(
SELECT COUNT(*) PACIFIC, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%S.PAC' + '%'
PATINDEX('%S.PAC%',MARKET) >0
GROUP BY AirLineCode
) TablePACIFIC ON TablePACIFIC.AirLineCode like '%' + SF.AirLineCode+ '%'
LEFT JOIN(
SELECT COUNT(*) DOMESTIC, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%DOMESTIC' + '%'
PATINDEX('%DOMESTIC%',MARKET) >0
GROUP BY AirLineCode
) TableDOMESTIC ON TableDOMESTIC.AirLineCode like '%' + SF.AirLineCode + '%'
) AS TableCount
WHERE Show = 'F'
--WHERE GROUP BY AirLineCode,AirlineName, FullName
那么就不显示。我写的n烦,感觉应该有好的方式,请大家再帮忙看看啊
谢谢!
1,多个left join,能否在一个里面把et统计出来啊,不要多个了 SELECT COUNT(*) INDIA, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%INDIA' + '%'
PATINDEX('%NDIA%',MARKET) >0
GROUP BY AirLineCode 2,Case那段更是不好,自己想不出好的解决方式 CASE WHEN
(
ISNULL(INDIA, 0)
+ ISNULL(MEMO, 0)
+ ISNULL(ASIA, 0)
+ ISNULL(LATIN, 0)
+ ISNULL(ATLANTIC, 0)
+ ISNULL(PACIFIC, 0)
+ ISNULL(DOMESTIC, 0)
) =0 THEN 'T' else 'F' END Show
。。
GROUP BY AirLineCode,AirlineName, FullName
HAVING
(
SUM(DISTINCT INDIACount)+
SUM(DISTINCT MEMOCount) +
SUM(DISTINCT ASIACount)+
SUM(DISTINCT LATINCount) +
SUM(DISTINCT ATLANTICCount) +
SUM(DISTINCT PACIFICCount) +
SUM(DISTINCT DOMESTICCount)
)>0
SELECT COUNT(*) INDIA, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%INDIA' + '%'
PATINDEX('%NDIA%',MARKET) >0
GROUP BY AirLineCode
)TableINDIA ON TableINDIA.AirLineCode like '%' + SF.AirLineCode + '%'
LEFT JOIN(
SELECT COUNT(*) MEMO, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%MEMO'+ '%'
PATINDEX('%MEMO%',MARKET) >0
GROUP BY AirLineCode
) TableMEMO ON TableMEMO.AirLineCode like '%' + SF.AirLineCode + '%'
LEFT JOIN(
SELECT COUNT(*) ASIA, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%ASIA' + '%'
PATINDEX('%ASIA%',MARKET) >0
GROUP BY AirLineCode
) TableASIA ON TableASIA.AirLineCode like '%' + SF.AirLineCode + '%'
LEFT JOIN(
SELECT COUNT(*) LATIN, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%LATIN' + '%'
PATINDEX('%LATIN%',MARKET) >0
GROUP BY AirLineCode
) TableLATIN ON TableLATIN.AirLineCode like '%' + SF.AirLineCode + '%'
LEFT JOIN(
SELECT COUNT(*) ATLANTIC, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%EUROPE' + '%'
PATINDEX('%EUROPE%',MARKET) >0
GROUP BY AirLineCode
) TableATLANTIC ON
TableATLANTIC.AirLineCode like '%' + SF.AirLineCode + '%'
--PATINDEX('%' + SF.AirLineCode + '%',TableATLANTIC.AirLineCode) >0
LEFT JOIN(
SELECT COUNT(*) PACIFIC, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%S.PAC' + '%'
PATINDEX('%S.PAC%',MARKET) >0
GROUP BY AirLineCode
) TablePACIFIC ON TablePACIFIC.AirLineCode like '%' + SF.AirLineCode+ '%'
LEFT JOIN(
SELECT COUNT(*) DOMESTIC, AirLineCode FROM View_UserContract
WHERE
--MARKET LIKE '%DOMESTIC' + '%'
PATINDEX('%DOMESTIC%',MARKET) >0
GROUP BY AirLineCode
) TableDOMESTIC ON TableDOMESTIC.AirLineCode like '%' + SF.AirLineCode + '%'
) AS TableCount
LEFT JOIN (
select AirLineCode,sum(INDIA) INDIA,sum(MEMO) MEMO,sum(ASIA) ASIA,sum(LATIN) LATIN
from(
SELECT AirLineCode,COUNT(*) INDIA,0 MEMO,0 ASIA,0 LATIN FROM View_UserContract
WHERE
--MARKET LIKE '%INDIA' + '%'
PATINDEX('%NDIA%',MARKET) >0
GROUP BY AirLineCode
union all SELECT AirLineCode,0 INDIA,COUNT(*) MEMO,0 ASIA,0 LATIN FROM View_UserContract
WHERE
--MARKET LIKE '%MEMO'+ '%'
PATINDEX('%MEMO%',MARKET) >0
GROUP BY AirLineCode
union all SELECT AirLineCode,0 INDIA,0 MEMO,COUNT(*) ASIA,0 LATIN FROM View_UserContract
WHERE
--MARKET LIKE '%ASIA' + '%'
PATINDEX('%ASIA%',MARKET) >0
GROUP BY AirLineCode
union all SELECT AirLineCode,0 INDIA,0 MEMO,0 ASIA,COUNT(*) LATIN FROM View_UserContract
WHERE
--MARKET LIKE '%LATIN' + '%'
PATINDEX('%LATIN%',MARKET) >0
GROUP BY AirLineCode
) ......
) group by AirLineCode ) TableDOMESTIC ON TableDOMESTIC.AirLineCode like '%' + SF.AirLineCode + '%'
) AS TableCount
考虑将统计结果保存起来,
然后定期执行 PATINDEX('%NDIA%',MARKET) >0 貌似跟直接 Like 没区别 多表查询能做的就是聚集索引跟索引合理安排, 可以在连接字段上建立索引,
select
SUBSTRING(A.AirLineCode,0,3) AirLineCode,
'(' + A.AirLineCode + ') ' + A.AirlineName AS FullName,
SUM(INDIA) INDIA,
SUM(MEMO) MEMO,
SUM(ASIA) ASIA,
SUM(LATIN) LATIN,
SUM(ATLANTIC) ATLANTIC,
SUM(PACIFIC) PACIFIC,
SUM(DOMESTIC) DOMESTIC
FROM(
SELECT AirLineCode,COUNT(*) INDIA,0 MEMO,0 ASIA,0 LATIN,0 ATLANTIC,0 PACIFIC,0 DOMESTIC
FROM View_UserContract
WHERE
--MARKET LIKE '%INDIA' + '%'
PATINDEX('%NDIA%',MARKET) >0
GROUP BY AirLineCode UNION all SELECT AirLineCode,0 INDIA,COUNT(*) MEMO,0 ASIA,0 LATIN,0 ATLANTIC,0 PACIFIC,0 DOMESTIC
FROM View_UserContract
WHERE
--MARKET LIKE '%MEMO'+ '%'
PATINDEX('%MEMO%',MARKET) >0
GROUP BY AirLineCode union all SELECT AirLineCode,0 INDIA,0 MEMO,COUNT(*) ASIA,0 LATIN,0 ATLANTIC,0 PACIFIC,0 DOMESTIC
FROM View_UserContract
WHERE
--MARKET LIKE '%ASIA' + '%'
PATINDEX('%ASIA%',MARKET) >0
GROUP BY AirLineCode
union all SELECT AirLineCode,0 INDIA,0 MEMO,0 ASIA,COUNT(*) LATIN ,0 ATLANTIC,0 PACIFIC,0 DOMESTIC
FROM View_UserContract
WHERE
--MARKET LIKE '%LATIN' + '%'
PATINDEX('%LATIN%',MARKET) >0
GROUP BY AirLineCode UNION ALL SELECT AirLineCode,0 INDIA,0 MEMO,0 ASIA,0 LATIN,COUNT(*) ATLANTIC,0 PACIFIC,0 DOMESTIC
FROM View_UserContract
WHERE
--MARKET LIKE '%EUROPE' + '%'
PATINDEX('%EUROPE%',MARKET) >0
GROUP BY AirLineCode UNION ALL SELECT AirLineCode,0 INDIA,0 MEMO,0 ASIA,0 LATIN,0 ATLANTIC,COUNT(*) PACIFIC,0 DOMESTIC
FROM View_UserContract
WHERE
--MARKET LIKE '%S.PAC' + '%'
PATINDEX('%S.PAC%',MARKET) >0
GROUP BY AirLineCode UNION ALL SELECT AirLineCode,0 INDIA,0 MEMO,0 ASIA,0 LATIN,0 ATLANTIC,0 PACIFIC,COUNT(*) DOMESTIC
FROM View_UserContract
WHERE
--MARKET LIKE '%DOMESTIC' + '%'
PATINDEX('%DOMESTIC%',MARKET) >0
GROUP BY AirLineCode
)Temp
INNER JOIN MST_Airlines A ON Temp.AirLineCode = A.AirLineCode
GROUP BY
SUBSTRING(A.AirLineCode,0,3),
'(' + A.AirLineCode + ') ' + A.AirlineName