子查询导致sql [英] subquery result in sql

问题描述

我的表格结果是这样的

 id Col1 col2 
  1   2   6  
  2   3   6  
  3   1   6  

now我希望col2的结果为sum（col1）值，这将显示在col2



所以为此我使用了查询

 选择 xx.id，xx.col1，xx.sum（col1）col2 < span class =code-keyword> from  
（ select  a.id，cout（*）col1 来自 
 table1 a  join  table2 b  on  a.id = b.id）xx 

这样做即可获得col1的重复总和现在我想要第一行中的总和以及其余的coloumn

它的resturn null值

 id Col1 col2 
  1   2   6  
  2   3   0  
  3   1   0  

解决方案

有一些表定义会帮助，但我相信这就是你想要的。

 选择 
 id 
，col1 
， case  
 何时 RowId =  1  然后 SUM（col1） OVER （）
  else   0  
  end   as  col2 
 来自（
 选择 
 id 
，COUNT（*） AS  Col1 
，ROW_NUMBER（） OVER （ order   by  id） as  RowId 
  from  Table1 
 < span class =code-keyword> group   by  id） AS  xx 

我不确定你想要实现什么，但看看例子：

  DECLARE   @ tmp  表（id  INT   IDENTITY （ 1 ， 1 ），Col1  INT ，Col2 < span class =code-keyword> INT ）
 
  INSERT   INTO   @ tmp （Col1）
  SELECT   2  
  UNION   ALL   SELECT   3  
  UNION   ALL   SELECT   1  
  UNION  所有  SELECT   6  
  UNION  所有  SELECT   9  
  UNION   ALL   SELECT   8  
  UNION   ALL   SELECT   4  
  UNION   ALL   SELECT   5  
 < span class =code-keyword> UNION   ALL   SELECT   7  
 
   -    select statement  
  SELECT  id，Col1，Col2 =（ SELECT  SUM（Col1） AS  Col2  FROM   @ tmp ）
  FROM   @ tmp  
 
   -   更新声明 
 更新  @ tmp  
  SET  Col2 =（ SELECT  SUM（Col1） AS  Col2  FROM   @ tmp ）
 
   -   显示更新值 
  SELECT  id，Col1，Col2 
  FROM   @ tmp  

Well my table result is like this

id  Col1 col2 
 1   2     6
 2   3     6
 3   1     6

now i want the result for the col2 as sum(col1) values and this will be displayed in the col2

so for this i used the query as

select xx.id , xx.col1  , xx.sum(col1) col2   from 
( select a.id , cout(*) col1 from 
table1 a join table2 b on a.id = b.id ) xx 

on doing so i m getting repeating sum for the col1 now i want the sum in first row and for rest of coloumn
it resturn null value

id  Col1 col2 
 1   2     6
 2   3     0
 3   1     0

解决方案

Having some table definitions would help, but I believe this is what you want.

select
     id
    ,col1
    ,case
          when RowId = 1 then SUM(col1) OVER()
          else 0
     end as col2
from (
       select
            id
           ,COUNT(*) AS Col1
           ,ROW_NUMBER() OVER(order by id) as RowId
       from Table1
       group by id) AS xx

I'm not sure what do you want to achieve, but have a look at example:

DECLARE @tmp TABLE (id INT IDENTITY(1,1), Col1 INT, Col2 INT)

INSERT INTO @tmp (Col1)
SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 1
UNION ALL SELECT 6
UNION ALL SELECT 9
UNION ALL SELECT 8
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 7

--select statement
SELECT id,  Col1, Col2 = (SELECT SUM(Col1) AS Col2 FROM @tmp)
FROM @tmp

--update statement
UPDATE @tmp
    SET Col2 = (SELECT SUM(Col1) AS Col2 FROM @tmp)

--display updated values
SELECT id,  Col1, Col2
FROM @tmp

这篇关于子查询导致sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！