| |

VerySource

 Forgot password?
 Register
Search
View: 948|Reply: 8

SQL minor problems

[Copy link]

3

Threads

10

Posts

11.00

Credits

Newbie

Rank: 1

Credits
11.00

 China

Post time: 2020-2-21 11:00:02
| Show all posts |Read mode
get:
ID Product ID time v1 v2 v3 v4 sums
1 1001 17: 10 0 0 0 0 0
2 1002 17:20 1 0 1 0 2
3 1003 17:30 0 1 0 0 1
4 1004 17: 40 0 ​​0 1 0 1
5 1005 17:50 0 1 0 0 0
sums 5 1 2 2 0 AA

AA = 3, if AA is the sum of sums! = 0, what to do ??
Reply

Use magic Report

0

Threads

49

Posts

35.00

Credits

Newbie

Rank: 1

Credits
35.00

 China

Post time: 2020-4-29 20:45:01
| Show all posts
What does the last sentence mean?
Reply

Use magic Report

3

Threads

10

Posts

11.00

Credits

Newbie

Rank: 1

Credits
11.00

 China

 Author| Post time: 2020-4-30 02:45:01
| Show all posts
The last item is to find the number of items whose sums are not equal to 0
Reply

Use magic Report

0

Threads

88

Posts

55.00

Credits

Newbie

Rank: 1

Credits
55.00

 China

Post time: 2020-4-30 08:30:01
| Show all posts
create table T (ID int, product ID char (4), time char (5), v1 int, v2 int, v3 int, v4 int, sums int)
insert T select 1, '1001', '17: 10 ', 0, 0, 0, 0, 0
union all select 2, '1002', '17: 20 ', 1, 0, 1, 0, 2
union all select 3, '1003', '17: 30 ', 0, 1, 0, 0, 1
union all select 4, '1004', '17: 40 ', 0, 0, 1, 0, 1
union all select 5, '1005', '17: 50 ', 0, 1, 0, 0, 1

select ID = rtrim (ID), product ID, '' time, rtrim (v1), rtrim (v2), rtrim (v3), rtrim (v4), rtrim (sums) sums from T
union all
select 'sums', count (*), '', rtrim (sum (v1)), rtrim (sum (v2)), rtrim (sum (v3)), rtrim (sum (v4)), 'AA' sums from T
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 China

Post time: 2020-4-30 11:15:02
| Show all posts
select id = sums, product ID = count (product ID), time = '', v1 = sum (v1), v2 = sum (v2), v3 = sum (v3), v4 = sum (v4), sums = sum (sums) from tablename
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 Unknown

Post time: 2020-4-30 12:30:01
| Show all posts
select id = sums, product ID = count (product ID), time = '', v1 = sum (v1), v2 = sum (v2), v3 = sum (v3), v4 = sum (v4), sums = sum (case when sums <> 0 then 1 else 0 end) from tablename
Reply

Use magic Report

0

Threads

49

Posts

35.00

Credits

Newbie

Rank: 1

Credits
35.00

 China

Post time: 2020-4-30 13:15:01
| Show all posts
sum (case sums when 0 then 0 else 1 end) as AA
Reply

Use magic Report

3

Threads

10

Posts

11.00

Credits

Newbie

Rank: 1

Credits
11.00

 China

 Author| Post time: 2020-5-1 22:45:01
| Show all posts
Thank you, the problem has been solved
Reply

Use magic Report

0

Threads

40

Posts

29.00

Credits

Newbie

Rank: 1

Credits
29.00

 China

Post time: 2020-5-2 15:00:01
| Show all posts
select ID = rtrim (ID), product ID, time, rtrim (v1), rtrim (v2), rtrim (v3), rtrim (v4), rtrim (sums) sums from #t
union all
select 'sums', count (*), '', rtrim (sum (v1)), rtrim (sum (v2)), rtrim (sum (v3)), rtrim (sum (v4)), sum (sums) sums from #t
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list