sql查询中的多个SELECT命令

本文关键字：SELECT 命令查询 sql | 更新日期: 2023-09-27 17:53:46

我想在sql中检索这种表:

    Type     Qad (type count)     Correct(No. of item without error)     Accuracy(%)
   type 1        3                                 3                            100
   type2         6                                 3                             50

我正在尝试使用这个查询，但它有一个错误:

SELECT `type` AS 'ReturnType', COUNT(*) AS 'QAd',
(SELECT COUNT(*) FROM bartran WHERE QASample='2' AND QAErrorID='1' AND `type`=ReturnType AND TimefileDate BETWEEN '2012-01-01' AND '2012-12-31' AS 'Correct', 
(SELECT COUNT(*) FROM bartran WHERE QASample='2' AND QAErrorID='1' AND `type`=ReturnType AND TimefileDate BETWEEN '2012-01-01' AND '2012-12-31') / COUNT(*) * 100) AS 'Accuracy' 
FROM bartran WHERE QASample='2'
AND TimefileDate BETWEEN '2012-01-01' AND '2012-12-31'
GROUP BY TYPE;

我现在是新手，希望有人能帮我解决这个问题。谢谢! !

sql查询中的多个SELECT命令

您在查询中缺少括号)，正确的是:

SELECT `type`  AS 'ReturnType', 
       Count(*)   AS 'QAd', 
       (SELECT Count(*) 
        FROM   bartran 
        WHERE  qasample = '2' 
               AND qaerrorid = '1' 
               AND `type` = returntype 
               AND timefiledate BETWEEN '2012-01-01' AND '2012-12-31') AS 
       'Correct', 
       (SELECT Count(*) 
        FROM   bartran 
        WHERE  qasample = '2' 
               AND qaerrorid = '1' 
               AND `type` = returntype 
               AND timefiledate BETWEEN '2012-01-01' AND '2012-12-31') / Count(* 
       ) * 100 AS 'Accuracy' 
FROM   bartran 
WHERE  qasample = '2' 
       AND timefiledate BETWEEN '2012-01-01' AND '2012-12-31' 
GROUP  BY type;

更好的查询应该是:

SELECT `type` AS 'ReturnType', 
       Count(*)  AS 'QAd', 
       Count(CASE 
               WHEN qaerrorid = '1' THEN 1 
               ELSE NULL 
             end)  AS 'Correct', 
       Count(CASE 
               WHEN qaerrorid = '1' THEN 1 
               ELSE NULL 
             end) / Count(*) * 100 AS 'Accuracy' 
FROM   bartran 
WHERE  qasample = '2' 
       AND timefiledate BETWEEN '2012-01-01' AND '2012-12-31' 
GROUP  BY type;