gpt4 book ai didi

php - 显示我还没有投票的用户 - MySQL

转载 作者:行者123 更新时间:2023-11-30 23:32:39 25 4
gpt4 key购买 nike

我正在尝试显示我尚未投票的所有用户!

CREATE TABLE IF NOT EXISTS `users` 
(`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(200) COLLATE utf8_unicode_ci NOT NULL,PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `whose_voted` (
`voter_user_id` int(11) NOT NULL,
`voted_on_user_id` int(11) NOT NULL,
PRIMARY KEY (`voter_user_id`,`voted_on_user_id`)
)

CREATE TABLE IF NOT EXISTS `votes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`yes_count` int(11) NOT NULL,
`beer_count` int(11) NOT NULL,
`total_votes` int(11) NOT NULL,
PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `user_votes` (
`users_id` int(11) NOT NULL,
`votes_id` int(11) NOT NULL,
PRIMARY KEY (`users_id`)
)

我有这个查询工作:

/* Show the Names of all users that have not been Voted on */
SELECT users.name
FROM users
LEFT JOIN user_votes ON users.id = users_id
LEFT JOIN votes ON votes_id = votes.id
WHERE votes.id IS NULL
ORDER BY users.name

这个查询也有效

/* Show all the User Names that I have voted on */
SELECT users.id AS 'User ID', users.name AS 'I\'ve Voted On'
FROM users
INNER JOIN whose_voted ON users.id = voted_on_user_id
WHERE whose_voted.voter_user_id = 32 /* my User ID */

我坚持要向用户显示我还没有投票!

** 更新工作查询 ****

/* Show a random users I have Not Voted For as of yet */
SELECT
unot.id, unot.name
FROM
users AS unot
WHERE NOT EXISTS (
/* Exclude the users you have voted on from the total user list */
SELECT uhave.id
FROM users AS uhave
INNER JOIN whose_voted ON uhave.id = voted_on_user_id
WHERE unot.id = uhave.id
AND (whose_voted.voter_user_id = 32)
)
AND (unot.id != 32)
AND (RAND()<(SELECT ((1/COUNT(*))*10) FROM users))
ORDER BY RAND()
LIMIT 1;

这个坏男孩一次吐出 1 个随机用户,我还没有投票!感谢您的帮助,非常感谢!

正如您所想象的,我正在对随机出现的人进行投票,这会将他们放入 whose_voted 表中,因此不会再次显示随机查询...任何想要简化我的代码的 SQL 专家?干杯米克

最佳答案

一种方法是将 WHERE NOT EXISTS 与上面的最后一个查询一起用作子查询。当然,未经测试,但应该可以。

SELECT
users.id AS `User ID`, users.name AS 'I\'ve not Voted On'
FROM
users unot
WHERE NOT EXISTS (
/* Exclude the users you have voted on from the total user list */
SELECT users.id AS
FROM users uhave
INNER JOIN whose_voted ON users.id = voted_on_user_id
WHERE whose_voted.voter_user_id = 32 /* my User ID */
AND unot.id = uhave.id
)

NOT EXISTS 子查询可能比 JOIN 慢。同样,未经测试。

SELECT
users.id AS `User ID`,
users.name AS 'I\'ve not Voted On'
FROM
users unot
LEFT JOIN (
SELECT users.id AS
FROM users uhave
INNER JOIN whose_voted ON users.id = voted_on_user_id
WHERE whose_voted.voter_user_id = 32 /* my User ID */
) uhave ON unot.id = uhave.id
/* NULL in the have voted subquery indicates user hasn't been voted on. */
WHERE uhave.id IS NULL

关于php - 显示我还没有投票的用户 - MySQL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9780838/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com