这个SQL怎么搞？

Question

-------数据
5 4995 1655 1  84 40 20
5 4995 1656 1  84 40 20
5 4995 1657 1  84 40 20
5 4995 1805 6  84 40 20
---------结果
=  添加一列
5 4995 1655 1  84 40 20   0.107142(--9/84)
5 4995 1656 1  84 40 20   NULL
5 4995 1657 1  84 40 20   NULL
5 4995 1805 6  84 40 20   NULL

Answer 1

--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([col1] int,[col2] int,[col3] int,[col4] int,[col5] int,[col6] int,[col7] int)
insert [ta]
select 5,4995,1655,1,84,40,20 union all
select 5,4995,1656,1,84,40,20 union all
select 5,4995,1657,1,84,40,20 union all
select 5,4995,1805,6,84,40,20--------------------------------查询开始------------------------------select *,sum(col4) over()*1.0/col5 from [ta]
/*
col1        col2        col3        col4        col5        col6        col7        
----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
5           4995        1655        1           84          40          20          0.107142857142
5           4995        1656        1           84          40          20          0.107142857142
5           4995        1657        1           84          40          20          0.107142857142
5           4995        1805        6           84          40          20          0.107142857142(4 行受影响)*/

Answer 2

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [int],[b] [int],[c] [int],[d] [int],[e] [int],[f] [int],[g] [int])
INSERT INTO [tb]
SELECT '5','4995','1655','1','84','40','20' UNION ALL
SELECT '5','4995','1656','1','84','40','20' UNION ALL
SELECT '5','4995','1657','1','84','40','20' UNION ALL
SELECT '5','4995','1805','6','84','40','20'
-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
    DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test]()
RETURNS DEC(18,6)
AS
BEGIN
RETURN(SELECT SUM(D) FROM TB)
END
GO
ALTER TABLE tb ADD h AS CAST(dbo.[fn_test]()/e AS DEC(18,6))
GO
SELECT * FROM [tb]
/*
a           b           c           d           e           f           g           h
----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
5           4995        1655        1           84          40          20          0.107143
5           4995        1656        1           84          40          20          0.107143
5           4995        1657        1           84          40          20          0.107143
5           4995        1805        6           84          40          20          0.107143(4 行受影响)*/

Answer 3

--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([col1] int,[col2] int,[col3] int,[col4] int,[col5] int,[col6] int,[col7] int)
insert [ta]
select 5,4995,1655,1,84,40,20 union all
select 5,4995,1656,1,84,40,20 union all
select 5,4995,1657,1,84,40,20 union all
select 5,4995,1805,6,84,40,20--------------------------------查询开始------------------------------select *,
col8=case when not exists
(
select 1 from [ta] where col3<a.col3
    ) 
    then  sum(col4) over()*1.0/col5 
    else null end 
from [ta] a
/*
col1        col2        col3        col4        col5        col6        col7        col8
----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
5           4995        1655        1           84          40          20          0.107142857142
5           4995        1656        1           84          40          20          NULL
5           4995        1657        1           84          40          20          NULL
5           4995        1805        6           84          40          20          NULL(4 行受影响)
*/

Answer 4

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [int],[b] [int],[c] [int],[d] [int],[e] [int],[f] [int],[g] [int])
INSERT INTO [tb]
SELECT '5','4995','1655','1','84','40','20' UNION ALL
SELECT '5','4995','1656','1','84','40','20' UNION ALL
SELECT '5','4995','1657','1','84','40','20' UNION ALL
SELECT '5','4995','1805','6','84','40','20'
-->SQL查询如下:SELECT *,h=(SELECT sum(D*1.) FROM tb)/e FROM [tb]
/*
a           b           c           d           e           f           g           h
----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
5           4995        1655        1           84          40          20          0.107142
5           4995        1656        1           84          40          20          0.107142
5           4995        1657        1           84          40          20          0.107142
5           4995        1805        6           84          40          20          0.107142(4 行受影响)
*/只是查询的话也可以参考这样

Answer 5

--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([col1] int,[col2] int,[col3] int,[col4] int,[col5] int,[col6] int,[col7] int)
insert [ta]
select 5,4995,1655,1,84,40,20 union all
select 5,4995,1656,1,84,40,20 union all
select 5,4995,1657,1,84,40,20 union all
select 5,4995,1805,6,84,40,20--------------------------------查询开始------------------------------select *,
col8=case when not exists
(
select 1 from [ta] where col3<a.col3
    ) 
    then  cast(sum(col4) over()*1.0/col5 as decimal(18,6))
    else null end 
from [ta] a
/*
col1        col2        col3        col4        col5        col6        col7        col8
----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
5           4995        1655        1           84          40          20          0.107143
5           4995        1656        1           84          40          20          NULL
5           4995        1657        1           84          40          20          NULL
5           4995        1805        6           84          40          20          NULL(4 行受影响)*/

Answer 6

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [int],[b] [int],[c] [int],[d] [int],[e] [int],[f] [int],[g] [int])
INSERT INTO [tb]
SELECT '5','4995','1655','1','84','40','20' UNION ALL
SELECT '5','4995','1656','1','84','40','20' UNION ALL
SELECT '5','4995','1657','1','84','40','20' UNION ALL
SELECT '5','4995','1805','6','84','40','20'
-->SQL查询如下:SELECT *,h=CASE C WHEN (SELECT TOP 1 C FROM TB WHERE A=T.a) THEN (SELECT sum(D*1.) FROM tb)/e END FROM [tb] t
/*
a           b           c           d           e           f           g           h
----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
5           4995        1655        1           84          40          20          0.107142
5           4995        1656        1           84          40          20          NULL
5           4995        1657        1           84          40          20          NULL
5           4995        1805        6           84          40          20          NULL(4 行受影响)
*/效果修理

Answer 7

怎么造个测试数据，都不对了？if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([col1] int,[col2] int,[col3] int,[col4] int,[col5] int,[col6] int,[col7] int)
insert [ta]
select 5,4995,1655,1,84,40,20 union all
select 5,4995,1656,1,84,40,20 union all
select 5,4995,1657,1,84,40,20 union all
select 5,4995,1805,6,84,40,20
UNION ALL
SELECT 6,4997,1655,0,100,40,20 UNION ALL
SELECT 6,4997,1656,1,100,40,20 UNION ALL
SELECT 6,4997,1657,1,100,40,20 UNION ALL
SELECT 6,4997,1805,1,100,40,20
--------------------------------查询开始------------------------------select *,
col8=case when not exists
    (
        select 1 from [ta] where col1=A.col1 AND col2=A.col2 AND  col3<a.col3
    ) 
    then  cast(sum(col4) over()*1.0/col5 as decimal(18,6))
    else null end 
from [ta] a

Answer 8

create table [ta]([col1] int,[col2] int,[col3] int,[col4] int,[col5] int,[col6] int,[col7] int)
insert [ta]
select 5,4995,1655,1,84,40,20 union all
select 5,4995,1656,1,84,40,20 union all
select 5,4995,1657,1,84,40,20 union all
select 5,4995,1805,6,84,40,20
UNION ALL
SELECT 6,4997,1655,0,100,40,20 UNION ALL
SELECT 6,4997,1656,1,100,40,20 UNION ALL
SELECT 6,4997,1657,1,100,40,20 UNION ALL
SELECT 6,4997,1805,1,100,40,20select t.* ,[添加一列]=
       (case when col3 = (select top 1 col3 from ta where col1 = t.col1 order by col3) then 
       (select sum(col4)*1.0 from ta where col1 = t.col1) else null end)/col5
from ta t drop table ta/*
col1        col2        col3        col4        col5        col6        col7        添加一列                       
----------- ----------- ----------- ----------- ----------- ----------- ----------- -------------------------- 
5           4995        1655        1           84          40          20          .107142857142
5           4995        1656        1           84          40          20          NULL
5           4995        1657        1           84          40          20          NULL
5           4995        1805        6           84          40          20          NULL
6           4997        1655        0           100         40          20          .030000000000
6           4997        1656        1           100         40          20          NULL
6           4997        1657        1           100         40          20          NULL
6           4997        1805        1           100         40          20          NULL（所影响的行数为 8 行）*/