开发者

SQL: Subquery assistance

I'm trying to SELECT two values in one statement instead of two statements. The first statement counts how many entries won for a specific contest that has an id of 18. The second statement counts the total quantity of prizes for that contest.

Query 1:

SELECT 

COUNT(*) FROM entries 

WHERE entries.contest=18

AND entries.won=1

Query 2

SELECT SUM( quantity ) FROM prizes WHERE contest=18

I want to have both so I could compare them on the server-side and act upon that comparison.. So if we had say 3 ent开发者_运维技巧rants who won the 18th contest and the total quantity of prizes for the 18th contest is 5, do something...

Schema:

CREATE TABLE `entries` (
  `id` int(2) unsigned NOT NULL auto_increment,
  `message_id` bigint(8) unsigned default NULL,
  `user` int(2) unsigned default NULL,
  `username` varchar(50) NOT NULL,
  `contest` int(2) unsigned default NULL,
  `message` text,
  `icon` text,
  `twitter_url` text,
  `follower` int(2) unsigned default NULL,
  `entry_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `won` int(1) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CREATE TABLE `prizes` (
  `id` int(2) unsigned NOT NULL auto_increment,
  `name` varchar(25) NOT NULL,
  `contest` int(2) NOT NULL,
  `quantity` int(2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

There is no users table because I control the entries, and I'm pretty sure I won't get any duplicates so that's why the user name, etc is stored for the entry row.


As the queries doesn't have anything in common at all, you would use two subqueries to get them in the same result:

select
  (select count(*) from entries where contest = 18 and won = 1) as wins,
  (select sum(quantity) from prizes where contest = 18) as quantity
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜