如何从sql表中选择数据 [英] how to select the data from sql table

问题描述

我的桌子是

 projecttaskid status projectid resid 
 ------------ ----------------------------------- 
 1暂停9 43 
 2 in进度9 44 
 3正在进行中9 43 
 4草案9 43 
 5暂停10 43 
 ----------------- ----------------------------- 

现在我想要



任务计数（具有状态！=草案）渣油43作为[assignedTaskcount]和总任务计数（具有状态！=草稿）为[Totaltaskcount]

其中projectid = 9 in single query



我希望输出为

 assignedTaskcount Totaltaskcount 
 ---------------------------------------- 
 2 3 
 ---------------------------------------- 

请提供查询帮助。我不能使用resi d直接在查询中，我要通过加入其他表来获取它。

但为了您的理解，请在这张桌子上帮助我。

解决方案

最简单的解决方案是将 SUM 与 CASE 语句结合使用：

  SELECT  
 SUM（ CASE   WHEN  resid =  43  那么  1   ELSE   0   END ）作为 assignedTaskcount，
 COUNT（ 1 ） As  Totaltaskcount 
  FROM  
 YourTable 
  WHERE  
 projectid =  9  
 和 
 status！= '  draft' 
; 

快速解决方案可以是

  SELECT （ SELECT  COUNT（projecttaskid） FROM  yourTable  WHERE  status！= '  draft'  AND  projectid = 9 和 resid =  43 ）assignedTaskcount，
（ SELECT  COUNT（projecttaskid） FROM  yourTable  WHERE  status！= '  draft'  AND  projectid = 9）Totaltaskcount 

为了获得最佳性能，您可以创建临时表并在该表中插入所有数据，然后在该表上进行这些查询

  DECLARE   @ temp   TABLE  
（
 projecttaskid  BIGINT ，
 status  VARCHAR （ 100 ），
 projectid  BIGINT ，
 resid < span class =code-keyword> BIGINT  
）
 
  INSERT   INTO   @ temp （projecttaskid，status，projectid，resid）
  SELECT  *  FROM  yourTable  WHERE  projectid =  9  和 status！= '  draft' 
 
  SELECT （ SELECT  COUNT（projecttaskid） FROM   @temp   WHERE  resid =  43 ）assignedTaskcount，
（ SELECT  COUNT（projecttaskid） FROM   @ temp ）Totaltaskcount 

My table is

projecttaskid    status      projectid resid
-----------------------------------------------
1                on hold        9       43
2                in progress    9       44
3                in progress    9       43
4                draft          9       43
5                on hold        10      43
----------------------------------------------

Now I want

taskcount (having status!=draft) of resid 43 as [assignedTaskcount] and total taskcount (having status!=draft) as[Totaltaskcount]
where projectid=9 in a single query

I want the output as

assignedTaskcount     Totaltaskcount
----------------------------------------
    2                    3
----------------------------------------

Please help by giving the query.I can't use resid as direct in query,I've to fetch it by joining with other table.
But for your easy understanding,just help me on this table.

解决方案

The simplest solution is to combine a SUM with a CASE statement:

SELECT
    SUM(CASE WHEN resid = 43 THEN 1 ELSE 0 END) As assignedTaskcount,
    COUNT(1) As Totaltaskcount
FROM
    YourTable
WHERE
    projectid = 9
And
    status != 'draft'
;

a quick solution could be

SELECT (SELECT  COUNT(projecttaskid) FROM yourTable WHERE status != 'draft' AND projectid=9 and  resid = 43) assignedTaskcount,
        (SELECT  COUNT(projecttaskid) FROM yourTable WHERE status != 'draft' AND projectid=9)  Totaltaskcount

for best performance you can create temp table and insert all your data in that table and then make these queries on that table

DECLARE @temp TABLE
(
	projecttaskid   BIGINT,
	status      VARCHAR(100),
	projectid BIGINT,
	resid BIGINT
)

INSERT INTO @temp ( projecttaskid ,status ,projectid ,resid)
SELECT * FROM yourTable WHERE projectid = 9 and status != 'draft'

SELECT (SELECT  COUNT(projecttaskid) FROM @temp WHERE  resid = 43) assignedTaskcount,
        (SELECT  COUNT(projecttaskid) FROM @temp)  Totaltaskcount

这篇关于如何从sql表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！