doucai1901 2016-05-11 03:44
浏览 127
已采纳

选择包含的两列> 1行具有相同的值

I have a table like this:

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: May 11, 2016 at 05:38 AM
-- Server version: 5.6.21
-- PHP Version: 5.6.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `jadwal`
--

-- --------------------------------------------------------

--
-- Table structure for table `data_kuliah`
--

CREATE TABLE IF NOT EXISTS `data_kuliah` (
`id_datkul` int(11) NOT NULL,
  `id_matkul` int(11) NOT NULL,
  `id_kelas` int(11) NOT NULL,
  `id_dosen` int(11) NOT NULL,
  `id_dosen2` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `data_kuliah`
--

INSERT INTO `data_kuliah` (`id_datkul`, `id_matkul`, `id_kelas`, `id_dosen`, `id_dosen2`) VALUES
(0, 2, 1, 14, 6),
(1, 3, 1, 14, 6),
(2, 4, 1, 12, 0),
(3, 1, 1, 16, 0),
(4, 9, 1, 6, 14),
(5, 7, 1, 8, 0),
(6, 6, 1, 8, 15),
(7, 22, 1, 12, 14),
(8, 23, 1, 7, 13),
(9, 24, 1, 10, 14),
(10, 26, 2, 8, 0),
(11, 27, 1, 8, 0),
(12, 28, 1, 10, 14),
(13, 28, 2, 14, 10),
(14, 29, 1, 10, 14),
(15, 29, 2, 14, 10),
(16, 30, 1, 15, 7),
(17, 31, 1, 15, 0),
(18, 33, 1, 16, 0),
(19, 69, 1, 15, 7),
(20, 69, 2, 7, 15),
(21, 70, 1, 15, 7),
(22, 70, 2, 7, 15),
(23, 45, 1, 7, 15),
(24, 46, 1, 7, 15),
(25, 47, 1, 14, 10),
(26, 48, 1, 14, 10),
(27, 49, 1, 9, 0),
(28, 49, 2, 9, 0),
(29, 50, 1, 14, 6),
(30, 62, 1, 8, 15),
(31, 65, 1, 15, 7),
(32, 68, 1, 15, 7),
(33, 26, 3, 8, 0);

-- --------------------------------------------------------

--
-- Table structure for table `data_waktu`
--

CREATE TABLE IF NOT EXISTS `data_waktu` (
`id_waktu` int(11) NOT NULL,
  `kd_hari` int(11) NOT NULL,
  `kd_jam` int(11) NOT NULL,
  `id_ruang` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `data_waktu`
--

INSERT INTO `data_waktu` (`id_waktu`, `kd_hari`, `kd_jam`, `id_ruang`) VALUES
(0, 1, 1, 1),
(1, 1, 2, 2),
(2, 1, 3, 3),
(3, 1, 4, 4),
(4, 1, 5, 6),
(5, 1, 6, 7),
(6, 1, 7, 1),
(7, 1, 8, 0),
(8, 1, 9, 0),
(9, 1, 10, 0),
(10, 1, 11, 0),
(11, 1, 12, 0),
(12, 1, 13, 0),
(13, 1, 14, 0),
(14, 1, 15, 0),
(15, 1, 16, 0),
(16, 1, 17, 0),
(17, 2, 1, 0),
(18, 2, 2, 0),
(19, 2, 3, 0),
(20, 2, 4, 0),
(21, 2, 5, 0),
(22, 2, 6, 0),
(23, 2, 7, 0),
(35, 2, 10, 0),
(36, 2, 11, 0),
(37, 2, 12, 0),
(38, 2, 13, 0),
(39, 2, 14, 0),
(40, 2, 15, 0),
(41, 2, 16, 0),
(42, 2, 17, 0),
(43, 3, 1, 0),
(44, 3, 2, 0),
(45, 3, 3, 0),
(46, 3, 4, 0),
(47, 3, 5, 0),
(48, 3, 6, 0),
(49, 3, 7, 0),
(51, 3, 9, 0),
(52, 3, 10, 0),
(53, 3, 11, 0),
(54, 3, 12, 0),
(56, 3, 13, 0),
(57, 3, 14, 0),
(58, 3, 15, 0),
(59, 3, 16, 0),
(60, 3, 17, 0),
(61, 4, 1, 0),
(62, 4, 2, 0),
(63, 4, 3, 0),
(64, 4, 4, 0),
(65, 4, 5, 0),
(66, 4, 6, 0),
(67, 4, 7, 0),
(68, 4, 8, 0),
(69, 4, 9, 0),
(70, 4, 10, 0),
(71, 4, 11, 0),
(72, 4, 12, 0),
(73, 4, 13, 0),
(74, 4, 14, 0),
(75, 4, 15, 0),
(76, 4, 16, 0),
(77, 4, 17, 0),
(78, 5, 1, 0),
(79, 5, 2, 0),
(80, 5, 3, 0),
(81, 5, 4, 0),
(82, 5, 5, 0),
(83, 5, 6, 0),
(84, 5, 7, 0),
(85, 5, 8, 0),
(86, 5, 9, 0),
(87, 5, 10, 0),
(88, 5, 11, 0),
(90, 5, 13, 0),
(91, 5, 14, 0),
(92, 5, 15, 0),
(93, 5, 16, 0),
(94, 5, 17, 0),
(95, 2, 8, 0),
(96, 2, 9, 0),
(97, 3, 8, 0),
(98, 5, 16, 0);

-- --------------------------------------------------------

--
-- Table structure for table `dosen`
--

CREATE TABLE IF NOT EXISTS `dosen` (
`id_dosen` int(11) NOT NULL,
  `kd_dosen` varchar(20) NOT NULL,
  `nip_dosen` varchar(20) DEFAULT NULL,
  `nm_dosen` varchar(200) DEFAULT NULL,
  `keterangan` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `dosen`
--

INSERT INTO `dosen` (`id_dosen`, `kd_dosen`, `nip_dosen`, `nm_dosen`, `keterangan`) VALUES
(6, 'D 03', '197401071999032001', 'Endang Sugiharti S.Si.,M.Kom.', ''),
(7, 'D 04', '197405172006041001', 'AlamsyahS.Si.,M.Kom.', ''),
(8, 'D 05', '197404202008121001', 'Much Aziz Muslim S.Kom., M.Kom.', ''),
(9, 'D 06', '197810252003122001', 'Florentina Yuni Arini, S.Kom., M.Kom.', ''),
(10, 'D 07', '198005252005011001', 'Riza Arifudin, S.Pd., M.Cs.', ''),
(12, 'D 09', '197001021999031002', 'Isa Akhlis S.Si., M.Si.', ''),
(13, 'D 10', '198707062014041003', 'Anggy Trisnawan Putra, S.Si.,M.Si.', ''),
(14, 'D 11', '198509102014071116', 'Aji Purwinarko, S.Si., M.Cs', ''),
(15, 'D 12', '198805012014041001', 'Budi Prasetiyo, S.Si., M.Kom.', ''),
(16, 'D 13', '-', 'Fajar Arif Setyawan, S.Pd., M.Pd.', '');

-- --------------------------------------------------------

--
-- Table structure for table `hari`
--

CREATE TABLE IF NOT EXISTS `hari` (
`kd_hari` int(11) NOT NULL,
  `hari` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `hari`
--

INSERT INTO `hari` (`kd_hari`, `hari`) VALUES
(1, 'senin'),
(2, 'selasa'),
(3, 'rabu'),
(4, 'kamis'),
(5, 'jumat');

-- --------------------------------------------------------

--
-- Table structure for table `jam`
--

CREATE TABLE IF NOT EXISTS `jam` (
`kd_jam` int(11) NOT NULL,
  `jam_kuliah` varchar(50) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `jam`
--

INSERT INTO `jam` (`kd_jam`, `jam_kuliah`) VALUES
(1, '07:00:00'),
(3, '08:40:00'),
(4, '09:30:00'),
(5, '10:00:00'),
(6, '11:00:00'),
(7, '12:00:00'),
(8, '12:30:00'),
(9, '12:40:00'),
(10, '13:00:00'),
(12, '15:00:00'),
(15, '15:30:00'),
(16, '16:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `kelas`
--

CREATE TABLE IF NOT EXISTS `kelas` (
`id_kelas` int(11) NOT NULL,
  `nm_kelas` varchar(50) NOT NULL,
  `keterangan` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `kelas`
--

INSERT INTO `kelas` (`id_kelas`, `nm_kelas`, `keterangan`) VALUES
(1, 'K - 1', ''),
(2, 'K - 2', ''),
(3, 'K - 3', '');

-- --------------------------------------------------------

--
-- Table structure for table `mata_kuliah`
--

CREATE TABLE IF NOT EXISTS `mata_kuliah` (
`id_matkul` int(11) NOT NULL,
  `kd_matkul` varchar(50) NOT NULL,
  `nm_matkul` varchar(50) NOT NULL,
  `sks` int(5) NOT NULL,
  `semester` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `mata_kuliah`
--

INSERT INTO `mata_kuliah` (`id_matkul`, `kd_matkul`, `nm_matkul`, `sks`, `semester`) VALUES
(1, 'M 01', 'Kalkulus', 3, 'Ganjil [1]'),
(2, 'M 02', 'Algoritma dan Pemrograman', 2, 'Ganjil [1]'),
(3, 'M 03', 'Praktik Algoritma dan Pemrograman', 1, 'Ganjil [1]'),
(4, 'M 04', 'Fisika', 3, 'Ganjil [1]'),
(6, 'M 06', 'Sistem Operasi', 2, 'Ganjil [1]'),
(7, 'M 07', 'Praktik Sistem Operasi', 1, 'Ganjil [1]'),
(9, 'M 08', 'Pengantar Teknologi Informatika', 2, 'Ganjil [1]'),
(22, 'M 21', 'Interaksi Manusia dan Komputer', 3, 'Ganjil [3]'),
(23, 'M 22', 'Teori Bahasa dan Otomata', 3, 'Ganjil [3]'),
(24, 'M 23', 'Teknik Kompilasi', 3, 'Ganjil [3]'),
(26, 'M 25', 'Jaringan Komputer', 2, 'Ganjil [3]'),
(27, 'M 26', 'Praktik Jaringan Komputer', 1, 'Ganjil [3]'),
(28, 'M 27', 'Sistem Basis Data', 2, 'Ganjil [3]'),
(29, 'M 28', 'Praktik Sistem Basis Data', 1, 'Ganjil [3]'),
(30, 'M 29', 'Analisis Algoritma', 3, 'Ganjil [3]'),
(31, 'M 30', 'Etika Profesi', 2, 'Ganjil [3]'),
(33, 'M 32', 'Teori Graph', 3, 'Ganjil [5]'),
(45, 'M 44', 'Kecerdasan Buatan', 3, 'Ganjil [5]'),
(46, 'M 45', 'Praktik Kecerdasan Buatan', 1, 'Ganjil [5]'),
(47, 'M 46', 'Grafika Komputer', 3, 'Ganjil [5]'),
(48, 'M 47', 'Praktik Grafika Komputer', 1, 'Ganjil [5]'),
(49, 'M 48', 'Komputer dan Masyarakat', 3, 'Ganjil [5]'),
(50, 'M 49', 'Sistem Pendukung Keputusan', 3, 'Ganjil [5]'),
(62, 'M 61', 'Keamanan Jaringan', 3, 'Ganjil [7]'),
(65, 'M 64', 'Jaringan Syaraf Tiruan', 3, 'Ganjil [7]'),
(68, 'M 67', 'Logika Fuzzy', 3, 'Ganjil [7]'),
(69, 'M 33', 'Teknik Multimedia', 3, 'Ganjil [5]'),
(70, 'M 34', 'Praktik Teknik Multimedia', 1, 'Ganjil [5]');

-- --------------------------------------------------------

--
-- Table structure for table `ruang`
--

CREATE TABLE IF NOT EXISTS `ruang` (
`id_ruang` int(11) NOT NULL,
  `kd_ruang` varchar(6) DEFAULT NULL,
  `nm_ruang` varchar(20) DEFAULT NULL,
  `kapasitas` int(6) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ruang`
--

INSERT INTO `ruang` (`id_ruang`, `kd_ruang`, `nm_ruang`, `kapasitas`) VALUES
(1, 'R 01', 'LAB 1', 0),
(2, 'R 02', 'D9 LAB. KOMP. II', 0),
(3, 'R 03', 'D2-314', 0),
(4, 'R 04', 'D2-310', 0),
(5, 'R 05', 'LAB ILKOM 1 D1-301 ', 0),
(6, 'R 06', 'LAB 2', 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `data_kuliah`
--
ALTER TABLE `data_kuliah`
 ADD PRIMARY KEY (`id_datkul`);

--
-- Indexes for table `data_waktu`
--
ALTER TABLE `data_waktu`
 ADD PRIMARY KEY (`id_waktu`);

--
-- Indexes for table `dosen`
--
ALTER TABLE `dosen`
 ADD PRIMARY KEY (`id_dosen`), ADD UNIQUE KEY `dsn_nama_key` (`nm_dosen`);

--
-- Indexes for table `hari`
--
ALTER TABLE `hari`
 ADD PRIMARY KEY (`kd_hari`);

--
-- Indexes for table `jam`
--
ALTER TABLE `jam`
 ADD PRIMARY KEY (`kd_jam`);

--
-- Indexes for table `kelas`
--
ALTER TABLE `kelas`
 ADD PRIMARY KEY (`id_kelas`);

--
-- Indexes for table `mata_kuliah`
--
ALTER TABLE `mata_kuliah`
 ADD PRIMARY KEY (`id_matkul`);

--
-- Indexes for table `ruang`
--
ALTER TABLE `ruang`
 ADD PRIMARY KEY (`id_ruang`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `data_kuliah`
--
ALTER TABLE `data_kuliah`
MODIFY `id_datkul` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=34;
--
-- AUTO_INCREMENT for table `data_waktu`
--
ALTER TABLE `data_waktu`
MODIFY `id_waktu` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=99;
--
-- AUTO_INCREMENT for table `dosen`
--
ALTER TABLE `dosen`
MODIFY `id_dosen` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=17;
--
-- AUTO_INCREMENT for table `hari`
--
ALTER TABLE `hari`
MODIFY `kd_hari` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `jam`
--
ALTER TABLE `jam`
MODIFY `kd_jam` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=17;
--
-- AUTO_INCREMENT for table `kelas`
--
ALTER TABLE `kelas`
MODIFY `id_kelas` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `mata_kuliah`
--
ALTER TABLE `mata_kuliah`
MODIFY `id_matkul` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=71;
--
-- AUTO_INCREMENT for table `ruang`
--
ALTER TABLE `ruang`
MODIFY `id_ruang` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

My SQL 

select (select hari.hari from data_waktu join hari on data_waktu.kd_hari=hari.kd_hari order by rand() limit 1) as 'HARI', (select jam.jam_kuliah from data_waktu join jam on data_waktu.kd_jam=jam.kd_jam order by rand() limit 1) as 'jam_kuliah', (select nm_ruang from ruang order by rand() limit 1) as 'nm_ruang', id_datkul from data_kuliah
Example output like this: Example output like this:

My question is, how to display the records only if the two columns contained more than one row have the same value?

I am very confused about this, please help

thank you

</div>
  • 写回答

1条回答 默认 最新

  • ds20021205 2016-05-11 05:56
    关注

    Try this query...

    SELECT *
FROM data_kuliah
WHERE id_dosen IN (
    SELECT id_dosen
    FROM data_kuliah
    GROUP BY id_dosen
    HAVING COUNT(id_datkul) > 1
    )
and
id_dosen2 IN (
    SELECT id_dosen2
    FROM data_kuliah
    GROUP BY id_dosen2
    HAVING COUNT(id_datkul) > 1
    )
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题？

悬赏问题

  • ¥15 为什么使用javacv转封装rtsp为rtmp时出现如下问题：[h264 @ 000000004faf7500]no frame？
  • ¥15 乘性高斯噪声在深度学习网络中的应用
  • ¥15 运筹学排序问题中的在线排序
  • ¥15 关于docker部署flink集成hadoop的yarn，请教个问题 flink启动yarn-session.sh连不上hadoop，这个整了好几天一直不行，求帮忙看一下怎么解决
  • ¥30 求一段fortran代码用IVF编译运行的结果
  • ¥15 深度学习根据CNN网络模型，搭建BP模型并训练MNIST数据集
  • ¥15 C++ 头文件/宏冲突问题解决
  • ¥15 用comsol模拟大气湍流通过底部加热（温度不同）的腔体
  • ¥50 安卓adb backup备份子用户应用数据失败
  • ¥20 有人能用聚类分析帮我分析一下文本内容嘛