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

hcpalex

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? ? ?

sjfox509

Recommendation: use two-day sentences
Article 1 Total calculation
The second article is to inquire

hcpalex

Specific point upstairs

迪迪安

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

獠牙精英

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

獠牙精英

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

hcpalex

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? ? ?

hcpalex

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

jadekylin

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

hcpalex

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? ?