| |

VerySource

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

Find a SQL statement ~~~ Master help ~~~

[Copy link]

1

Threads

1

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 China

Post time: 2020-2-21 01:00:01
| Show all posts |Read mode
Records in the table allip

id ip sid
1 127.0.0.1 a
2 127.0.0.2 a
3 127.0.0.3 a
4 127.0.0.1 a
5 127.0.0.4 b
6 127.0.0.1 a
7 127.0.0.5 c
8 127.0.0.6 d

Now I find the sum of the number of all unique IPs with sid = a (that is, an IP can only appear once)
The resulting value should be 3

I use
select count (ip) as c from allip where sid = 'a' group by ip
The answer is the number of each IP instead of their sum. How to write SQL? Ask for advice ~~~~~~~~~~~~~~~~
Reply

Use magic Report

0

Threads

49

Posts

35.00

Credits

Newbie

Rank: 1

Credits
35.00

 China

Post time: 2020-4-29 12:00:01
| Show all posts
select count (distinct ip) as c from allip where sid = 'a'
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-4-29 14:30:01
| Show all posts
select count (distinct ip) as c from allip where sid = 'a' group by ip
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 Brazil

Post time: 2020-4-29 17:15:01
| Show all posts
select count (ip) as c from allip where sid = 'a' and ip in (select distinct ip from allip)
Reply

Use magic Report

0

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-5-1 00:30:01
| Show all posts
--Test Data
create table t (id int identity (1,1), ip nvarchar (20), sid char (1))
insert t (ip, sid)
select '127.0.0.1', 'a' union all
select '127.0.0.2', 'a' union all
select '127.0.0.3', 'a' union all
select '127.0.0.1', 'a' union all
select '127.0.0.4', 'b' union all
select '127.0.0.3', 'a' union all
select '127.0.0.5', 'c' union all
select '127.0.0.6', 'd'
select count (distinct ip) from t where sid = 'a'
drop table t
Reply

Use magic Report

0

Threads

40

Posts

29.00

Credits

Newbie

Rank: 1

Credits
29.00

 China

Post time: 2020-5-1 13:00:02
| Show all posts
select count (distinct ip) from #temp where sid = 'a'
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-5-2 02:15:01
| Show all posts
select count (ip) from (select ip from allip where sid = 'a' group by ip having count (ip) = 1) m
Reply

Use magic Report

1

Threads

23

Posts

18.00

Credits

Newbie

Rank: 1

Credits
18.00

 China

Post time: 2020-5-2 08:30:01
| Show all posts
Learn it!
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 United States

Post time: 2020-5-2 14:45:01
| Show all posts
select count (distinct ip) from allip where sid = 'a'
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