日常总结

1.对于硬编码的解决方法：

在类所需要引用用的类，采用在构造器其中设置参数为接口

两个Integer不能直接比较值

 

2.((Integer)session).intValue()  == ((Integer)str).intValue()

要用上面的方法比较session和str的值（都是Integer类型的）

3.注意map中不能有key相同的值，否则会取得

4.sql中select的结果集作为新的查询“表”

SELECT
    abc.ID,ab.COMPANY_ID
FROM
    (
        SELECT DISTINCT
            a.ID
        FROM
            accident_measure a,
            company com
        WHERE
            a.ID = 1
    ) abc,
    accident_measure ab

 实例2：

　　StringBuffer sql=new StringBuffer("select t.uId AS userId, t.uName AS name, t.cName AS clsName, t.stuNum AS stuNum, ")
            .append(" t.SCORE AS score, t.TOP AS top, t.END_TIME AS endTime , t.testStatus AS studnetTestStatus, t.approveStatus AS approveStatus")
            .append(" from ( select t1.uId, t1.uName, t1.cId,t1.cName, t1.stuNum, t2.SCORE, CASE WHEN t2.top IS NULL THEN 10000 ELSE t2.top END AS TOP,")
            .append(" t2.END_TIME, t2.testStatus, t2.approveStatus from ( select us.id AS uId, us.`NAME` AS uName,c.ID as cId, c.CLS_NAME AS cName,")
            .append(" stu.STU_NUM AS stuNum from tb_class c, tb_student stu, tb_user us, tb_test_paper_class tpc WHERE tpc.EXAM_ID =? ")
            .append(" AND tpc.CLASS_ID = c.ID AND c.ID = stu.CLASS_ID AND stu.USER_ID = us.ID and us.STATUS=0 ) t1 ")
            .append(" LEFT JOIN ( select us.id AS uId, us.`NAME` AS uName, c.CLS_NAME AS cName, stu.STU_NUM AS stuNum, tc.SCORE AS SCORE,")
            .append(" tc.TOP AS top, tc.END_TIME END_TIME, tc.TEST_STATUS testStatus, tc.APPROVE_STATUS approveStatus from tb_test_score tc,")
            .append(" tb_student stu, tb_class c, tb_user us WHERE tc.EXAM_ID =? AND tc.STU_ID = us.ID AND us.ID = stu.USER_ID ")
            .append(" AND stu.CLASS_ID = c.ID ) t2 ON t1.uid = t2.uid ) t where 1=1 ");   

 

 5.mysql中解决查询某个表时不能修改该表问题

UPDATE company c
SET c.`NAME` = 'abcdef'
WHERE
    c.ID = (
        SELECT abc.ID FROM(
                SELECT DISTINCT    a.ID FROM accident_measure a, company com WHERE a.ID = 1 ) abc

)

引入一个“中间表”，这种方式的效率应该不怎么高，可以查分成多个sql实现该功能。

 

6.声明一个变量接收这个查询的值,在sql后面用来计算

SELECT
    @examCount \\:= (
        SELECT
            COUNT(1) Complete
        FROM
            tb_test_score tb1,
            tb_exam_manage tb2,
            tb_test_paper tb3,
            tb_user tb4,
            tb_student tb5
        WHERE
            tb1.EXAM_ID = tb2.ID
        AND tb2.TEST_PAGE_ID = tb3.ID
        AND tb1.STU_ID = tb4.ID
        AND tb1.CLASS_ID = tb5.CLASS_ID
        AND tb1.STU_ID = tb5.USER_ID
        AND tb4. STATUS = 0
        AND tb1.EXAM_ID =?
        AND tb1.CLASS_ID = t1.CLASS_ID
    ) Complete,
    count(1) -@examCount Notfinished
FROM
    tb_student t1,
    tb_user t2
WHERE
    t1.CLASS_ID =?
AND t1.USER_ID = t2.ID
AND t2. STATUS = 0

 实例2：

SELECT
    address,@tes := (SELECT m.id FROM modeltest m where m.id=1) testde
FROM
    modeltest
WHERE @tes>0