| |

VerySource

 Forgot password?
 Register
Search
View: 24|Reply: 12

Everyone, help me, I'm dying, thank you! !! !!

[Copy link]

1

Threads

6

Posts

7

Credits

Newbie

Rank: 1

Credits
7

 China

Post time: 2020-2-11 23:30:02
| Show all posts |Read mode
3 main watches
eqs_users: user information table field id (primary key) other information fields
ws_users_info: user details table field uid other information fields
ws_cn_sell: Sales information table Field uid Other information fields
Three tables, id, uid, uid are related. First, you must count the customers who have posted more than 10 sales information, and you need to paginate
This is my statement for fetching data
SELECT a.id, a.username, a.name, a.ip, b. *, (SELECT count (*) FROM ws_cn_sell sell WHERE sell.uid = b.uid) AS sell_num FROM eqs_users a LEFT JOIN ws_users_info b ON a .id = b.uid WHERE 1 = 1 HAVING sell_num> = 10 ORDER BY a.register_date DESC LIMIT 0, 20
Below is the pagination I used to count the customers who posted more than 10 sales information
SELECT COUNT (*) FROM eqs_users a LEFT JOIN ws_users_info b ON a.id = b.uid WHERE 1 = 1 HAVING sell_num> = 10
Obviously the statistics are wrong because there is no sell_num at all.
I would like to implement the above function, how do I write a statement, both to search it out and to total the pages? ? ?
Reply

Use magic Report

0

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 Invalid IP Address

Post time: 2020-4-12 19:15:01
| Show all posts
Recommendation: use two-day sentences
Article 1 Total calculation
The second article is to inquire
Reply

Use magic Report

1

Threads

6

Posts

7

Credits

Newbie

Rank: 1

Credits
7

 China

 Author| Post time: 2020-4-28 12:00:01
| Show all posts
Specific point upstairs
Reply

Use magic Report

0

Threads

13

Posts

12

Credits

Newbie

Rank: 1

Credits
12

 China

Post time: 2020-4-29 13:15:02
| Show all posts
It is recommended that you perform grouping, group the second table, and then extract the user ID with more than 10 messages, and check it in the other table. If you are using MySQL above 5.0, you can use statement nesting, but if you are below 5.0, you have to use two SQL statements, this is no way T_T
Reply

Use magic Report

0

Threads

22

Posts

14

Credits

Newbie

Rank: 1

Credits
14

 China

Post time: 2020-5-10 21:00:01
| Show all posts
The statistical total can be done like this:
SELECT SUM (u_num) AS total
FROM
(
SELECT COUNT (*) AS u_num FROM `ws_cn_sell` GROUP BY uid

) AS t
Reply

Use magic Report

0

Threads

22

Posts

14

Credits

Newbie

Rank: 1

Credits
14

 China

Post time: 2020-5-12 14:45:02
| Show all posts
Suddenly remembered that the above did not add more than 10 conditions.
Try this below,
SELECT SUM (u_num) AS total
FROM
(
SELECT IF (count (*)> 10, count (*), 0) as u_num
FROM `ws_cn_sell`
GROUP BY uid
) AS t
Reply

Use magic Report

1

Threads

6

Posts

7

Credits

Newbie

Rank: 1

Credits
7

 China

 Author| Post time: 2020-5-14 10:00:01
| Show all posts
Thank you very much upstairs, I tried it, yes, modify one of your clerical errors
SUM (u_num) => COUNT (u_num)
By the way, if I still have a table ws_cn_buy (purchasing information table, the associated field is also uid), that is, to publish customers who sell more than 10 sales information and more than 5 purchase information, how to count the total? ? ?
Reply

Use magic Report

1

Threads

6

Posts

7

Credits

Newbie

Rank: 1

Credits
7

 China

 Author| Post time: 2020-5-15 18:45:02
| Show all posts
SELECT SUM (u_num) AS total
FROM
(
SELECT IF (count (*)> 10, count (*), 0) as u_num
FROM `ws_cn_sell`
GROUP BY uid
) AS t
This sentence is wrong, I don't know why
I changed it and it was right:
SELECT COUNT (u_num) AS total
FROM
(
SELECT count (*) as u_num
FROM `ws_cn_sell`
GROUP BY uid having u_num> = 10
) AS t
Reply

Use magic Report

0

Threads

7

Posts

8

Credits

Newbie

Rank: 1

Credits
8

 China

Post time: 2020-6-3 17:45:01
| Show all posts
1. You should figure out what you want to do?
The information of "to count the customers who publish more than 10 sales information" comes from "ws_cn_sell: Sales Information Table"
Everything else is auxiliary information
So there is no need to use LEFT JOIN, just connect
2. The last sentence of sql, I haven't figured out what you are doing since I saw it yesterday
Reply

Use magic Report

1

Threads

6

Posts

7

Credits

Newbie

Rank: 1

Credits
7

 China

 Author| Post time: 2020-6-4 13:00:02
| Show all posts
This is actually the case
select uid,count(*) as aa from ws_cn_sell group by uid having aa>10 order by aa desc
This is to take out customers who publish more than 10 pieces of sale information

I now want to count the total number of customers who publish more than 10 pieces of sales information, so that they can be used for pagination:
select uid,count(*) as aa from ws_cn_sell group by uid having aa>10 order by aa desc
I don't know how to count the total number of customers in this sentence? ?
Reply

Use magic Report

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

Points Rules

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

Quick Reply To Top Return to the list