· 8 years ago · Jul 06, 2018, 06:24 PM
1SELECT
2 l.*
3FROM license AS l
4JOIN (
5 SELECT l.license_id, COUNT(u.user_id) AS amount
6 FROM license AS l
7 JOIN user AS u ON u.license_id = l.license_id
8 GROUP BY l.license_id
9) AS t
10 ON l.license_id = t.license_id
11WHERE t.amount < l.amount;
12
13
14-- Adminer 3.3.3 MySQL dump
15
16SET NAMES utf8;
17SET foreign_key_checks = 0;
18SET time_zone = 'SYSTEM';
19SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
20
21INSERT INTO `items` (`id`, `name`) VALUES
22(1, 'MFS Unit'),
23(2, 'MFE Unit'),
24(3, 'Advanced Circuit'),
25(4, 'Advanced Machine'),
26(5, 'Lapotron Crystal'),
27(6, 'Lapis Lazuli Dye'),
28(7, 'Electronic Circuit'),
29(8, 'Energy Crystal'),
30(9, 'Insulated Copper Cable'),
31(10, 'Redstone (Dust)'),
32(11, 'Refined Iron (Ingot)'),
33(12, 'Iron (Ingot)'),
34(13, 'Iron (Dust)'),
35(15, 'Diamond'),
36(16, 'Copper (Ingot)'),
37(17, 'Copper (Dust)'),
38(18, 'Rubber'),
39(19, 'Glowstone (Dust)'),
40(20, 'Machine (Block)'),
41(21, 'Advanced Alloy'),
42(22, 'Carbon Plate'),
43(23, 'Combined Carbon Fibers'),
44(24, 'Carbon Fiber'),
45(25, 'Coal (Dust)'),
46(26, 'Bronze (Ingot)'),
47(27, 'Tin (Ingot)'),
48(28, 'Tin (Dust)');
49
50DROP TABLE IF EXISTS `license`;
51CREATE TABLE `license` (
52 `license_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
53 `license_key` varchar(20) CHARACTER SET ascii NOT NULL,
54 `amount` int(10) unsigned NOT NULL,
55 `company` varchar(20) NOT NULL,
56 PRIMARY KEY (`license_id`)
57) ENGINE=InnoDB DEFAULT CHARSET=utf8;
58
59INSERT INTO `license` (`license_id`, `license_key`, `amount`, `company`) VALUES
60(1, '1111111111111', 2, 'Kans'),
61(2, '2222222222222', 5, 'ABH');
62
63DROP TABLE IF EXISTS `user`;
64CREATE TABLE `user` (
65 `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
66 `license_id` int(10) unsigned NOT NULL,
67 `name` varchar(20) NOT NULL,
68 PRIMARY KEY (`user_id`),
69 KEY `license_id` (`license_id`),
70 CONSTRAINT `user_ibfk_1` FOREIGN KEY (`license_id`) REFERENCES `license` (`license_id`)
71) ENGINE=InnoDB DEFAULT CHARSET=utf8;
72
73INSERT INTO `user` (`user_id`, `license_id`, `name`) VALUES
74(1, 1, 'Dino'),
75(2, 2, 'Jef'),
76(3, 1, 'Hans'),
77(4, 2, 'Jef 2');
78
79-- 2012-03-14 13:19:06