求一查询语句

一个表如下
id  userid   类型    状态   时间
1   11        aa      0     2009-10-10 10:10:10
2   11        aa      1     2009-10-11 10:10:10
3   22        bb      0     2009-10-12 10:10:10
4   22        bb      1     2009-10-13 10:10:10
查询这个表，如果状态=0的话记录插入到table1中（table1和这个表结构一样），状态=1时从table1中查询出userid，类型相等的数据合并成一条插入到一个新表中。
望高手指点

解决方案 »

  1.   

    insert into table1 select * from 表 where 状态=0状态=1时从table1中查询出userid，类型相等的数据合并成一条插入到一个新表中--这个没理解楼主要怎么做
      

  2.   

    例如 table1中有数据如下
    id  userid  类型    状态  时间 
    1  11        aa      0    2009-10-10 10:10:10 你查出的数据当状态=1时的数据如下
    id  userid  类型    状态  时间 
    10  11        aa      1    2009-10-15 10:10:10 将table1中的数据和这条数据合并插入的到table2中
    table2的结构如下：
    userid1   类型1   状态1   时间1   userid2   类型2   状态2   时间2
      

  3.   

    表结构就是这样的，可能我表述的不够清楚
    3个表， table（源数据表） table1（状态0的表） table2（用来统计的表）
    第一个月从源数据表数据中统计一次，状态0的插入到table1中（手动执行）
    第二个月统计时按照时间或者id的顺序来查询源数据表，如果状态0插入table1，如果状态1 则根据这条数据
         从table1表中查询出userid和类型相同的一条数据联合插入到table2表中
    以后每月执行一次状态1时插入数据详解：
    例如 table1中有数据如下 
    id  userid  类型    状态  时间 
    1  11        aa      0    2009-10-10 10:10:10 当从源数据表中查出的数据当状态=1时的数据如下 
    id  userid  类型    状态  时间 
    10  11        aa      1    2009-10-15 10:10:10 将table1中的数据和这条数据合并插入的到table2（统计表）中 
    table2的结构如下： 
    userid1  类型1  状态1  时间1  userid2  类型2  状态2  时间2
    不知道这样说大家能不能清楚点
      

  4.   


    select * from t1 a left join t1 b on a.userid=b.userid where a.状态='0' and b.状态='1'
      

  5.   

    比较好的办法就是你直接贴出create table xx 语句
    insert into ... 语句。然后你期望的结果的样子。这样大家可以不用担心对语言上的理解上的偏差。 根据你的数据，做出你需要的结果。
      

  6.   

    DROP TABLE IF EXISTS `t1`;CREATE TABLE `t1` (
      `id` int(11) NOT NULL default '0',
      `userid` varchar(50) default NULL,
      `otype` varchar(100) default NULL,
      `zt` varchar(50) default NULL,
      `dtime` timestamp NOT NULL default '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `t1` */insert  into `t1`(`id`,`userid`,`otype`,`zt`,`dtime`) values 
    (135,'18991341985','135000000000000000704','0','2008-12-20 10:23:59'),
    (136,'18991341985','135000000000000000704','3','2008-12-20 10:24:14'),
    (137,'18991341985','135000000000000000704','0','2008-12-20 10:24:40'),
    (138,'18991341985','135000000000000000704','3','2008-12-20 10:24:55'),
    (139,'18991341985','135000000000000000704','0','2008-12-20 10:26:06'),
    (140,'18991341985','135000000000000000704','3','2008-12-20 10:26:21'),
    (141,'01891c2a6cf32c6205e1a','135000000000000000704','0','2008-12-20 10:29:33'),
    (142,'8618973144175','135000000000000000704','0','2008-12-20 10:50:42'),
    (143,'18945184322','135000000000000000704','0','2008-12-20 11:04:23'),
    (144,'07714f4ecf6a3e4fa46ad','135000000000000000704','0','2008-12-20 11:16:15'),
    (145,'18979172142','135000000000000000704','0','2008-12-20 11:33:40'),
    (146,'18910044343','135000000000000000704','0','2008-12-20 11:36:55'),
    (147,'07716f1b2124769823401','135000000000000000704','0','2008-12-20 11:37:49'),
    (148,'01891bd800057df8fcb03','135000000000000000704','0','2008-12-20 11:38:46'),
    (149,'07716f1b2124769823401','135000000000000000704','3','2008-12-20 11:43:09'),
    (150,'01891bd800057df8fcb03','135000000000000000704','3','2008-12-20 11:47:54'),
    (151,'8618959740074','135000000000000000704','0','2008-12-20 11:48:58'),
    (152,'18907504237','135000000000000000704','0','2008-12-20 12:07:49'),
    (153,'12345678912','135000000000000000704','0','2008-12-20 12:09:23'),
    (154,'8618973144173','135000000000000000704','0','2008-12-20 12:10:02'),
    (155,'18979172142','135000000000000000704','3','2008-12-20 12:10:36'),
    (750,'18971710722','135000000000000000704','0','2009-01-01 09:44:00'),
    (751,'18971710722','135000000000000000704','3','2009-01-01 09:49:33'),
    (752,'8618905444044','135000000000000000704','0','2009-01-01 11:31:09'),
    (753,'8618953100923','135000000000000000704','0','2009-01-01 13:47:34'),
    (754,'18908076789','135000000000000000704','0','2009-01-01 15:58:23'),
    (755,'8618951850254','135000000000000000704','0','2009-01-01 18:06:14'),
    (756,'18995210171','135000000000000000704','0','2009-01-02 09:22:12'),
    (757,'18995210171','135000000000000000704','0','2009-01-02 09:22:12'),
    (758,'18995210171','135000000000000000704','0','2009-01-02 09:22:12'),
    (759,'18907956869','135000000000000000704','0','2009-01-02 19:12:59'),
    (760,'18931844308','135000000000000000704','0','2009-01-02 22:14:07'),
    (761,'18931844308','135000000000000000704','3','2009-01-02 22:16:14'),
    (762,'18986333333','135000000000000000704','0','2009-01-03 18:13:56'),
    (763,'18999440064','135000000000000000704','0','2009-01-03 23:53:05'),
    (764,'18982173585','135000000000000000704','0','2009-01-04 14:56:55'),
    (765,'8618918912096','135000000000000000704','0','2009-01-04 15:03:08'),
    (766,'18979172047','135000000000000000704','0','2009-01-04 17:13:34'),
    (767,'8618908381110','135000000000000000704','0','2009-01-04 17:40:39'),
    (768,'8618908963315','135000000000000000704','0','2009-01-04 17:45:51'),
    (769,'8618978240823','135000000000000000704','3','2009-01-04 20:35:06'),
    (770,'18943224639','135000000000000000704','0','2009-01-05 08:45:48'),
    (771,'18907198288','135000000000000000704','0','2009-01-05 11:14:34'),
    (772,'18971880501','135000000000000000704','0','2009-01-05 14:26:53'),
    (773,'18971880501','135000000000000000704','3','2009-01-05 22:49:17'),
    (774,'18999414135','135000000000000000704','0','2009-01-05 23:38:18'),
    (775,'18931844308','135000000000000000704','0','2009-01-06 00:04:45'),
    (776,'18931844308','135000000000000000704','3','2009-01-06 00:06:08'),
    (777,'18908004305','135000000000000000704','0','2009-01-06 01:35:41'),
    (778,'18971132501','135000000000000000704','0','2009-01-06 10:41:27'),
    (779,'8618903574067','135000000000000000704','0','2009-01-06 11:25:38'),
    (780,'8618903574067','135000000000000000704','3','2009-01-06 11:28:12'),
    (781,'18910044360','135000000000000000704','3','2009-01-06 13:28:59'),
    (782,'18986322863','135000000000000000704','0','2009-01-06 17:34:17'),
    (783,'8618903582040','135000000000000000704','0','2009-01-07 09:18:10'),
    (784,'8618903594067','135000000000000000704','0','2009-01-07 09:40:52'),
    (785,'8618903594067','135000000000000000704','3','2009-01-07 09:47:03'),
    (786,'8618935000354','135000000000000000704','0','2009-01-07 10:34:27'),
    (787,'8618935000354','135000000000000000704','3','2009-01-07 10:51:10'),
    (792,'18931633484','135000000000000000704','3','2009-01-07 18:58:04'),
    (793,'8618903424072','135000000000000000704','0','2009-01-08 13:01:11'),
    (794,'8618903424072','135000000000000000704','3','2009-01-08 13:05:55'),
    (919,'18907575389','135000000000000000704','0','2009-01-31 07:41:56'),
    (920,'18907575389','135000000000000000704','3','2009-01-31 07:43:39'),
    (921,'18907575389','135000000000000000704','0','2009-01-31 07:50:58'),
    (922,'18980805568','135000000000000000704','0','2009-01-31 10:35:48'),
    (923,'18927000366','135000000000000000704','0','2009-01-31 20:48:34');
    /* table1（状态0的表） */
    create table t2 select * from t1 where zt=0 and dtime>'2008-12-01' and dtime<'2009-01-01'/* 统计09年1月份的数据查询源数据表t1，如果状态0插入t2，如果状态3 则根据这条数据 
        从t1表中查询出userid=userid and otype=otype 的一条数据联合插入到一个新表中  */
      

  7.   

    上边写错注释了t2的原始数据就是这条sql语句查出来的，以后每个月统计 zt=0的插入进去
      

  8.   

    mysql> select * from t1;
    +-----+-----------------------+-----------------------+------+---------------------+
    | id  | userid                | otype                 | zt   | dtime               |
    +-----+-----------------------+-----------------------+------+---------------------+
    | 135 | 18991341985           | 135000000000000000704 | 0    | 2008-12-20 10:23:59 |
    | 136 | 18991341985           | 135000000000000000704 | 3    | 2008-12-20 10:24:14 |
    | 137 | 18991341985           | 135000000000000000704 | 0    | 2008-12-20 10:24:40 |
    | 138 | 18991341985           | 135000000000000000704 | 3    | 2008-12-20 10:24:55 |
    | 139 | 18991341985           | 135000000000000000704 | 0    | 2008-12-20 10:26:06 |
    | 140 | 18991341985           | 135000000000000000704 | 3    | 2008-12-20 10:26:21 |
    | 141 | 01891c2a6cf32c6205e1a | 135000000000000000704 | 0    | 2008-12-20 10:29:33 |
    | 142 | 8618973144175         | 135000000000000000704 | 0    | 2008-12-20 10:50:42 |
    | 143 | 18945184322           | 135000000000000000704 | 0    | 2008-12-20 11:04:23 |
    | 144 | 07714f4ecf6a3e4fa46ad | 135000000000000000704 | 0    | 2008-12-20 11:16:15 |
    | 145 | 18979172142           | 135000000000000000704 | 0    | 2008-12-20 11:33:40 |
    | 146 | 18910044343           | 135000000000000000704 | 0    | 2008-12-20 11:36:55 |
    | 147 | 07716f1b2124769823401 | 135000000000000000704 | 0    | 2008-12-20 11:37:49 |
    | 148 | 01891bd800057df8fcb03 | 135000000000000000704 | 0    | 2008-12-20 11:38:46 |
    | 149 | 07716f1b2124769823401 | 135000000000000000704 | 3    | 2008-12-20 11:43:09 |
    | 150 | 01891bd800057df8fcb03 | 135000000000000000704 | 3    | 2008-12-20 11:47:54 |
    | 151 | 8618959740074         | 135000000000000000704 | 0    | 2008-12-20 11:48:58 |
    | 152 | 18907504237           | 135000000000000000704 | 0    | 2008-12-20 12:07:49 |
    | 153 | 12345678912           | 135000000000000000704 | 0    | 2008-12-20 12:09:23 |
    | 154 | 8618973144173         | 135000000000000000704 | 0    | 2008-12-20 12:10:02 |
    | 155 | 18979172142           | 135000000000000000704 | 3    | 2008-12-20 12:10:36 |
    | 750 | 18971710722           | 135000000000000000704 | 0    | 2009-01-01 09:44:00 |
    | 751 | 18971710722           | 135000000000000000704 | 3    | 2009-01-01 09:49:33 |
    | 752 | 8618905444044         | 135000000000000000704 | 0    | 2009-01-01 11:31:09 |
    | 753 | 8618953100923         | 135000000000000000704 | 0    | 2009-01-01 13:47:34 |
    | 754 | 18908076789           | 135000000000000000704 | 0    | 2009-01-01 15:58:23 |
    | 755 | 8618951850254         | 135000000000000000704 | 0    | 2009-01-01 18:06:14 |
    | 756 | 18995210171           | 135000000000000000704 | 0    | 2009-01-02 09:22:12 |
    | 757 | 18995210171           | 135000000000000000704 | 0    | 2009-01-02 09:22:12 |
    | 758 | 18995210171           | 135000000000000000704 | 0    | 2009-01-02 09:22:12 |
    | 759 | 18907956869           | 135000000000000000704 | 0    | 2009-01-02 19:12:59 |
    | 760 | 18931844308           | 135000000000000000704 | 0    | 2009-01-02 22:14:07 |
    | 761 | 18931844308           | 135000000000000000704 | 3    | 2009-01-02 22:16:14 |
    | 762 | 18986333333           | 135000000000000000704 | 0    | 2009-01-03 18:13:56 |
    | 763 | 18999440064           | 135000000000000000704 | 0    | 2009-01-03 23:53:05 |
    | 764 | 18982173585           | 135000000000000000704 | 0    | 2009-01-04 14:56:55 |
    | 765 | 8618918912096         | 135000000000000000704 | 0    | 2009-01-04 15:03:08 |
    | 766 | 18979172047           | 135000000000000000704 | 0    | 2009-01-04 17:13:34 |
    | 767 | 8618908381110         | 135000000000000000704 | 0    | 2009-01-04 17:40:39 |
    | 768 | 8618908963315         | 135000000000000000704 | 0    | 2009-01-04 17:45:51 |
    | 769 | 8618978240823         | 135000000000000000704 | 3    | 2009-01-04 20:35:06 |
    | 770 | 18943224639           | 135000000000000000704 | 0    | 2009-01-05 08:45:48 |
    | 771 | 18907198288           | 135000000000000000704 | 0    | 2009-01-05 11:14:34 |
    | 772 | 18971880501           | 135000000000000000704 | 0    | 2009-01-05 14:26:53 |
    | 773 | 18971880501           | 135000000000000000704 | 3    | 2009-01-05 22:49:17 |
    | 774 | 18999414135           | 135000000000000000704 | 0    | 2009-01-05 23:38:18 |
    | 775 | 18931844308           | 135000000000000000704 | 0    | 2009-01-06 00:04:45 |
    | 776 | 18931844308           | 135000000000000000704 | 3    | 2009-01-06 00:06:08 |
    | 777 | 18908004305           | 135000000000000000704 | 0    | 2009-01-06 01:35:41 |
    | 778 | 18971132501           | 135000000000000000704 | 0    | 2009-01-06 10:41:27 |
    | 779 | 8618903574067         | 135000000000000000704 | 0    | 2009-01-06 11:25:38 |
    | 780 | 8618903574067         | 135000000000000000704 | 3    | 2009-01-06 11:28:12 |
    | 781 | 18910044360           | 135000000000000000704 | 3    | 2009-01-06 13:28:59 |
    | 782 | 18986322863           | 135000000000000000704 | 0    | 2009-01-06 17:34:17 |
    | 783 | 8618903582040         | 135000000000000000704 | 0    | 2009-01-07 09:18:10 |
    | 784 | 8618903594067         | 135000000000000000704 | 0    | 2009-01-07 09:40:52 |
    | 785 | 8618903594067         | 135000000000000000704 | 3    | 2009-01-07 09:47:03 |
    | 786 | 8618935000354         | 135000000000000000704 | 0    | 2009-01-07 10:34:27 |
    | 787 | 8618935000354         | 135000000000000000704 | 3    | 2009-01-07 10:51:10 |
    | 792 | 18931633484           | 135000000000000000704 | 3    | 2009-01-07 18:58:04 |
    | 793 | 8618903424072         | 135000000000000000704 | 0    | 2009-01-08 13:01:11 |
    | 794 | 8618903424072         | 135000000000000000704 | 3    | 2009-01-08 13:05:55 |
    | 919 | 18907575389           | 135000000000000000704 | 0    | 2009-01-31 07:41:56 |
    | 920 | 18907575389           | 135000000000000000704 | 3    | 2009-01-31 07:43:39 |
    | 921 | 18907575389           | 135000000000000000704 | 0    | 2009-01-31 07:50:58 |
    | 922 | 18980805568           | 135000000000000000704 | 0    | 2009-01-31 10:35:48 |
    | 923 | 18927000366           | 135000000000000000704 | 0    | 2009-01-31 20:48:34 |
    +-----+-----------------------+-----------------------+------+---------------------+
    67 rows in set (0.00 sec)mysql> select * from t2;
    +-----+-----------------------+-----------------------+------+---------------------+
    | id  | userid                | otype                 | zt   | dtime               |
    +-----+-----------------------+-----------------------+------+---------------------+
    | 135 | 18991341985           | 135000000000000000704 | 0    | 2008-12-20 10:23:59 |
    | 137 | 18991341985           | 135000000000000000704 | 0    | 2008-12-20 10:24:40 |
    | 139 | 18991341985           | 135000000000000000704 | 0    | 2008-12-20 10:26:06 |
    | 141 | 01891c2a6cf32c6205e1a | 135000000000000000704 | 0    | 2008-12-20 10:29:33 |
    | 142 | 8618973144175         | 135000000000000000704 | 0    | 2008-12-20 10:50:42 |
    | 143 | 18945184322           | 135000000000000000704 | 0    | 2008-12-20 11:04:23 |
    | 144 | 07714f4ecf6a3e4fa46ad | 135000000000000000704 | 0    | 2008-12-20 11:16:15 |
    | 145 | 18979172142           | 135000000000000000704 | 0    | 2008-12-20 11:33:40 |
    | 146 | 18910044343           | 135000000000000000704 | 0    | 2008-12-20 11:36:55 |
    | 147 | 07716f1b2124769823401 | 135000000000000000704 | 0    | 2008-12-20 11:37:49 |
    | 148 | 01891bd800057df8fcb03 | 135000000000000000704 | 0    | 2008-12-20 11:38:46 |
    | 151 | 8618959740074         | 135000000000000000704 | 0    | 2008-12-20 11:48:58 |
    | 152 | 18907504237           | 135000000000000000704 | 0    | 2008-12-20 12:07:49 |
    | 153 | 12345678912           | 135000000000000000704 | 0    | 2008-12-20 12:09:23 |
    | 154 | 8618973144173         | 135000000000000000704 | 0    | 2008-12-20 12:10:02 |
    +-----+-----------------------+-----------------------+------+---------------------+
    15 rows in set (0.00 sec)mysql>楼主啊，你期望的最终结果是什么，请象这样表述出来。我期望的结果如下：mysql> select * from t2;
    +-----+-----------------------+-----------------------+------+---------------------+
    | id  | userid                | otype                 | zt   | dtime               |
    +-----+-----------------------+-----------------------+------+---------------------+
    | 135 | 18991341985           | 135000000000000000704 | 1    | 2008-12-20 10:23:59 |
    | 137 | 18991341985           | 135000000000000000704 | 1    | 2008-12-20 10:24:40 |
    | 139 | 18991341985           | 135000000000000000704 | 1    | 2008-12-20 10:26:06 |
    | 141 | 01891c2a6cf32c6205e1a | 135000000000000000704 | 1    | 2008-12-20 10:29:33 |
    | 142 | 8618973144175         | 135000000000000000704 | 1    | 2008-12-20 10:50:42 |
    | 143 | 18945184322           | 135000000000000000704 | 1    | 2008-12-20 11:04:23 |
    | 144 | 07714f4ecf6a3e4fa46ad | 135000000000000000704 | 1    | 2008-12-20 11:16:15 |
    | 145 | 18979172142           | 135000000000000000704 | 1    | 2008-12-20 11:33:40 |
    | 146 | 18910044343           | 135000000000000000704 | 1    | 2008-12-20 11:36:55 |
    | 147 | 07716f1b2124769823401 | 135000000000000000704 | 1    | 2008-12-20 11:37:49 |
    | 148 | 01891bd800057df8fcb03 | 135000000000000000704 | 1    | 2008-12-20 11:38:46 |
    | 151 | 8618959740074         | 135000000000000000704 | 1    | 2008-12-20 11:48:58 |
    | 152 | 18907504237           | 135000000000000000704 | 1    | 2008-12-20 12:07:49 |
    | 153 | 12345678912           | 135000000000000000704 | 1    | 2008-12-20 12:09:23 |
    | 154 | 8618973144173         | 135000000000000000704 | 1    | 2008-12-20 12:10:02 |
    +-----+-----------------------+-----------------------+------+---------------------+
    15 rows in set (0.00 sec)
      

  9.   

    难道你不能权限上面你提供的数据，给出基于这些数据的正确答案吗？
    或者说下面这个结果你认为哪而不对？如果你认为下面的不对，那么正确的又是什么？ 很简单的一件事，只需要你写你正确的结果。
    +-----+-----------------------+-----------------------+------+---------------------+
    | id  | userid                | otype                 | zt   | dtime               |
    +-----+-----------------------+-----------------------+------+---------------------+
    | 135 | 18991341985           | 135000000000000000704 | 1    | 2008-12-20 10:23:59 |
    | 137 | 18991341985           | 135000000000000000704 | 1    | 2008-12-20 10:24:40 |
    | 139 | 18991341985           | 135000000000000000704 | 1    | 2008-12-20 10:26:06 |
    | 141 | 01891c2a6cf32c6205e1a | 135000000000000000704 | 1    | 2008-12-20 10:29:33 |
    | 142 | 8618973144175         | 135000000000000000704 | 1    | 2008-12-20 10:50:42 |
    | 143 | 18945184322           | 135000000000000000704 | 1    | 2008-12-20 11:04:23 |
    | 144 | 07714f4ecf6a3e4fa46ad | 135000000000000000704 | 1    | 2008-12-20 11:16:15 |
    | 145 | 18979172142           | 135000000000000000704 | 1    | 2008-12-20 11:33:40 |
    | 146 | 18910044343           | 135000000000000000704 | 1    | 2008-12-20 11:36:55 |
    | 147 | 07716f1b2124769823401 | 135000000000000000704 | 1    | 2008-12-20 11:37:49 |
    | 148 | 01891bd800057df8fcb03 | 135000000000000000704 | 1    | 2008-12-20 11:38:46 |
    | 151 | 8618959740074         | 135000000000000000704 | 1    | 2008-12-20 11:48:58 |
    | 152 | 18907504237           | 135000000000000000704 | 1    | 2008-12-20 12:07:49 |
    | 153 | 12345678912           | 135000000000000000704 | 1    | 2008-12-20 12:09:23 |
    | 154 | 8618973144173         | 135000000000000000704 | 1    | 2008-12-20 12:10:02 |
    +-----+-----------------------+-----------------------+------+---------------------+