create table djjx ( djbh varchar(20), --单据编号 key spid varchar(20), --商品ID shl int --数量 ) --其中二行数据: insert into djjx values('dj0001','sp0001',300) insert into djjx values('dj0001','sp0002',100) --表二: create table sphwph ( spid varchar(20), --商品ID key hw varchar(20), --货位 key ph varchar(20), --批号 key shl int --数量 ) 其中几行数据: insert into sphwph values('sp0001','hw0001','ph001',50) insert into sphwph values('sp0001','hw0001','ph002',40) insert into sphwph values('sp0001','hw0002','ph002',90) insert into sphwph values('sp0001','hw0003','ph003',500) insert into sphwph values('sp0001','hw0004','ph005',1000) insert into sphwph values('sp0002','hw0001','ph006',90) insert into sphwph values('sp0002','hw0002','ph009',100) /* 求得到如下结果或一张临时表: djbh spid hw ph shl dj0001 sp0001 hw001 ph001 50 dj0001 sp0001 hw001 ph002 40 dj0001 sp0001 hw002 ph002 90 dj0001 sp0001 hw003 ph003 120 dj0001 sp0002 hw001 ph006 90 dj0001 sp0002 hw002 ph009 10 */ ;with t as( select id=row_number()over(partition by a.spid order by getdate()), a.djbh,a.spid,a.shl as total,b.hw,b.ph,b.shl from djjx a inner join sphwph b on a.spid=b.spid ), m as( select id,djbh,spid,(total-shl) as total,hw,ph,shl from t where id=1 union all select a.id,a.djbh,a.spid,b.total-a.shl,a.hw,a.ph,a.shl from t a inner join m b on a.id=b.id+1 and a.spid=b.spid ) select djbh,spid,hw,ph, case when total<0 then shl-ABS(total) else shl end as shl from m where case when total<0 then shl-ABS(total) else shl end>0 order by spid,id /* djbh spid hw ph shl dj0001 sp0001 hw0001 ph001 50 dj0001 sp0001 hw0001 ph002 40 dj0001 sp0001 hw0002 ph002 90 dj0001 sp0001 hw0003 ph003 120 dj0001 sp0002 hw0001 ph006 90 dj0001 sp0002 hw0002 ph009 10 */有点像先进先出的东东。实在不想写，测试数据整的我机器都卡了
然后从开票表里面找客户id是3，开票日期在回款日期2010-11-26之前的开票"你看记录中
"金额108000 客户id是10，
然后从开票表里面找客户id是10，开票日期在回款日期2012-1-3之前的开票"
数据中
客户id是10开票日期全在2012-1-3之前你的开票和汇款很难对一起
3636 2010-3-16 无 512 3
3637 2010-3-16 无 36096 3
3638 2010-7-30 无 16380 3
3639 2010-7-30 无 9248 3
3640 2010-7-30 无 62832 3
3641 2011-12-1 1392913 22320 4
3642 2010-6-7 无 2856 7
3643 2010-6-7 无 19992 7
3644 2011-3-12 无 4998 7
3645 2011-3-23 无 2856 7
3646 2010-12-20 无 24750 8
3647 2010-12-20 无 150000 8
3648 2011-4-21 无 3570 10
3649 2011-4-21 无 3060 10
3650 2011-4-21 无 18360 10
3651 2011-4-21 无 35640 10
3652 2011-4-21 无 272160 10
3653 2011-4-21 无 82620 10
3654 2011-4-21 无 58320 10
3655 2011-4-21 无 19440 10
3656 2011-4-21 无 11220 10
3657 2011-4-21 无 3060 10
3658 2011-4-21 无 10200 10
3659 2011-4-21 无 194400 10
3660 2011-4-21 无 74520 10
3661 2011-4-21 无 197640 10
3662 2011-4-21 无 12750 10
3663 2011-4-21 无 1530 10
3664 2011-4-21 无 243000 10
3665 2011-4-21 无 45360 10
3666 2011-4-21 无 178200 10
3667 2011-4-21 无 10200 10
3668 2011-11-11 1392840 21600 10
3669 2011-11-11 1392840 205200 10
3670 2011-11-11 1392840 205200 10
3671 2011-11-11 1392839 10800 10
3672 2011-11-11 1392839 10800 10
3673 2011-11-11 1392839 99180 10
3674 2011-11-11 1392839 311220 10
3675 2011-11-11 1392841 10800 10
3676 2011-11-11 1392841 205200 10
3677 2010-12-24 无 126480 12
3678 2010-12-24 无 210800 12
3679 2010-12-24 无 182718.9 12
3680 2010-9-6 无 123552 13
3681 2010-9-6 无 0 13
3682 2010-11-24 无 23400 13
3683 2010-11-24 无 0 13
3684 2011-3-23 无 82680 13
3685 2011-3-23 无 0 13
3686 2011-5-21 62600 0 13
3687 2011-5-21 62600 18360 13
3688 2011-5-21 62600 176280 13
3689 2011-5-21 62599 0 13
3690 2011-5-21 62599 20400 13
3691 2011-5-21 62599 218400 13
3692 2011-5-21 62601 0 13
3693 2011-5-21 62601 46410 13
3694 2011-5-21 62601 173940 13
3695 2011-5-21 62601 229320 13
3696 2011-8-5 1265930 0 13
3697 2011-8-5 1265930 20400 13
3698 2011-8-5 1265930 156000 13
3699 2011-8-5 1265930 240240 13
3700 2011-8-5 1265930 0 13
3701 2011-8-5 1265930 39780 13
3702 2011-8-5 1265930 123240 13
3703 2011-3-5 无 1740 14
3704 2011-4-7 无 0 14
3705 2011-4-7 无 169650 14
3706 2011-4-7 无 25080 14
3707 2011-7-5 839763 15660 14
3708 2011-7-5 839763 1740 14
3709 2011-8-1 839994 27840 14
3710 2011-1-17 无 1608 19
3711 2011-1-17 无 4080 19
3712 2011-1-17 无 21840 19
3713 2011-1-17 无 6240 19
3714 2011-1-17 无 10920 19
3715 2011-1-17 无 46800 19
3716 2011-1-17 无 52260 19
3717 2011-1-17 无 23400 19
3718 2011-1-17 无 480 19
3719 2011-1-17 无 39000 19
3720 2011-1-17 无 2099.99988 19
3721 2011-1-17 无 24180 19
3722 2011-4-25 无 402 19
3723 2011-4-25 无 4500 19
3724 2011-4-25 无 5628 19
3725 2011-4-25 无 360 19
3726 2011-4-25 无 2250 19
3727 2011-4-25 无 3618 19
3728 2011-4-25 无 24120 19
3729 2011-4-25 无 20250 19
3730 2011-4-25 无 180000 19
3731 2011-4-25 无 3618 19
3732 2011-4-25 无 1350 19
3733 2011-4-25 无 8844 19
3734 2011-4-25 无 90750 19
3735 2011-4-25 无 0 19
3736 2011-4-25 无 8640 19
3737 2011-4-25 无 234750 19
3738 2011-5-20 167777 3840 19
3739 2011-5-20 167777 25500 19
3740 2011-5-20 167777 8442 19
3741 2011-5-20 167777 63000 19
3742 2011-5-20 167777 450 19
3743 2011-5-20 167777 37500 19
3744 2011-6-24 839729 13680 19
3745 2011-6-24 839729 17100 19
3746 2011-8-23 841440 96078 19
3747 2011-8-23 841440 28800 19
3748 2011-9-22 739461 2709 19
3749 2011-9-22 739461 34884 19
3750 2011-9-22 739461 73134 19
3751 2011-9-22 739461 38718 19
3752 2011-10-31 741871 1200 19
3753 2011-10-31 741871 1400 19
3754 2010-6-9 无 499380 20
3755 2011-7-27 839990 0 20
3756 2011-7-27 839990 25500.15 20
3757 2011-7-27 839990 223077.7692 20
3758 2011-7-27 839990 0 20
3759 2011-7-27 839990 0 20
3760 2011-7-27 839990 13260.078 20
3761 2011-7-27 839990 93599.064 20
3762 2011-7-27 839990 6630.039 20
3763 2011-7-27 839990 60059.3994 20
3764 2011-7-27 839990 234777.6522 20
3765 2011-7-27 839990 0 20
3766 2011-7-27 839990 44370.261 20
3767 2011-7-27 839990 173158.2684 20
3768 2011-7-27 839990 7140.042 20
3769 2011-7-27 839990 99839.0016 20
3770 2011-7-27 839992 0 20
3771 2011-7-27 839992 9690.057 20
3772 2011-7-27 839992 229317.7068 20
3773 2011-7-27 839992 0 20
3774 2011-7-27 839992 29070.171 20
3775 2011-7-27 839992 223857.7614 20
3776 2011-7-27 839992 0 20
3777 2011-7-27 839992 29070.171 20
3778 2011-7-27 839992 223857.7614 20
3779 2011-7-27 839992 0 20
3780 2011-7-27 839992 8670.051 20
3781 2011-7-27 839992 67079.3292 20
3782 2011-9-2 737346 25080 20
3783 2011-9-2 737346 226200 20
3784 2011-9-23 739474 8567.4186 20
3785 2011-9-23 739475 115254.9918 20
3786 2011-9-23 739475 154187.28 20
3787 2011-9-23 739475 2698.2774 20
3788 2011-9-23 739475 385.4682 20
3789 2011-9-23 739475 144936.0432 20
3790 2011-9-23 739475 154187.28 20
3791 2011-9-23 739475 186181.1406 20
3792 2011-9-23 739475 144936.0432 20
3793 2011-9-23 739475 154187.28 20
3794 2011-9-23 739475 124120.7604 20
3795 2011-9-23 739475 96367.05 20
3796 2011-9-23 739475 1349.1387 20
3797 2011-9-23 739475 125277.165 20
3798 2011-9-23 739475 68998.8078 20
3799 2011-9-23 739475 25055.433 20
3800 2011-9-23 739475 80948.322 20
3801 2011-9-23 739475 1156.4046 20
3802 2011-1-17 无 66048 21
3803 2011-1-17 无 118272 21
3804 2011-1-17 无 55296 21
3805 2011-1-17 无 36864 21
3806 2011-1-17 无 78336 21
3807 2011-1-17 无 145184 21
3808 2011-1-17 无 0 21
3809 2011-1-17 无 0 21
3810 2011-1-17 无 0 21
3811 2011-1-17 无 0 21
3812 2011-1-17 无 0 21
3813 2012-1-9 1395657 8568 21
3814 2012-1-9 1395657 4032 21
3815 2012-1-9 1395657 6048 21
3816 2012-1-9 1395657 13104 21
3817 2012-1-9 1395657 9072 21
3818 2012-1-9 1395657 24192 21
3819 2012-1-9 1395657 12096 21
3820 2012-1-9 1395657 116736 21
3821 2012-1-9 1395657 221184 21
3822 2012-1-9 1395657 24192 21
3823 2012-1-9 1395657 76000 21
3824 2012-1-9 1395657 0 21
3825 2012-1-9 1395657 0 21
3826 2010-10-12 无 9576 22
3827 2010-10-12 无 43008 22
3828 2010-10-12 无 0 22
3829 2011-2-24 无 87552 22
3830 2011-2-24 无 0 22
3831 2011-7-1 839749 0 22
3832 2011-7-1 839749 20640 22
3833 2011-7-1 839749 127680 22
3834 2011-7-1 839749 105600 22
3835 2011-7-1 839749 0 22
3836 2011-7-1 839749 11352 22
3837 2011-7-1 839749 70224 22
3838 2011-7-1 839749 58080 22
3839 2011-7-1 839749 0 22
1629 2010-11-26 266716.56 3
1630 2011-7-20 408816 3
1631 2011-12-1 22320 4
1632 2011-12-13 30702 7
1633 2011-5-13 1475250 10
1634 2011-12-15 972000 10
1635 2012-1-13 108000 10
1636 2010-4-28 256400.13 12
1637 2011-1-21 263598.77 12
1638 2010-8-5 111196.8 13
1639 2010-12-16 23400 13
1640 2011-4-12 74412 13
1641 2011-6-29 404703 13
1642 2011-6-29 214920 13
1643 2011-9-5 175176 13
1644 2011-9-9 12355.2 13
1645 2011-9-21 521694 13
1646 2011-3-4 1740 14
1647 2011-4-18 194730 14
1648 2011-6-28 15660 14
1649 2011-7-5 1740 14
1650 2011-7-28 27840 14
1651 2011-10-21 8700 14
1652 2011-11-15 4350 14
1653 2011-1-26 279708 19
1654 2011-3-30 35880 19
1655 2011-5-10 589080 19
1656 2011-6-4 191732 19
1657 2011-7-7 61002 19
1658 2011-9-6 124878 19
1659 2011-10-9 16746 19
1660 2011-10-19 149445 19
1661 2011-12-12 2600 19
1662 2011-3-25 82495.2 20
1663 2011-4-19 186830.85 20
1664 2011-5-25 31635.74 20
1665 2011-6-25 35276.4 20
1666 2011-8-25 8790.31 20
1667 2011-9-21 39446.5 20
1668 2011-10-15 1353771.1 20
1669 2011-11-18 3240412.73 20
1670 2011-12-6 188459.36 20
1671 2011-3-3 500000 21
1672 2012-1-13 515224 21
1673 2011-1-19 52584 22
1674 2011-5-19 87552 22
1675 2011-9-16 1369770 22
1676 2011-12-28 107868 22
1677 2011-12-28 47538 22
1678 2012-1-18 231132 22
1679 2012-1-19 1546098 22
1680 2011-4-1 104280 30
1681 2011-1-29 50000 31
1682 2011-8-2 9504 32
1683 2011-9-5 87492 32
1684 2010-6-28 200000 34
1685 2010-8-17 100000 34
1686 2011-1-14 319620 34
1687 2011-5-10 214000 34
1688 2011-9-2 400000 34
1689 2011-12-20 240000 34
1690 2012-1-12 158780 34
1691 2011-2-23 92120 36
1692 2011-3-21 16800 36
1693 2011-4-2 16800 36
1694 2011-4-15 11200 36
1695 2011-6-14 11200 36
1696 2011-8-10 11000 36
1697 2011-8-12 15680 36
1698 2011-9-14 16800 36
1699 2011-9-15 200 36
1700 2011-9-26 11200 36
1701 2011-9-28 44800 36
1702 2011-10-27 16800 36
1703 2011-11-1 50400 36
1704 2011-11-11 11200 36
1705 2011-5-30 71820 37
1706 2011-6-27 54000 37
1707 2011-7-27 27000 37
1708 2011-11-18 16740 37
1709 2011-1-24 59400 38
1710 2011-6-15 950334 41
1711 2011-7-27 199920 41
1712 2011-8-24 2087736 41
1713 2011-10-8 421260 41
1714 2011-11-30 46410 41
1715 2011-3-30 71820 43
1716 2011-5-20 71820 43
1717 2011-6-27 153588 43
1718 2011-7-18 153900 43
1719 2011-10-8 217170 43
1720 2011-10-19 148770 43
1721 2011-11-7 143640 43
1722 2011-12-13 72180 43
1723 2010-12-20 19200 45
1724 2011-12-27 14160 45
1725 2011-1-27 4608 46
1726 2010-12-11 14301.6 47
1727 2010-12-17 8726.4 47
1728 2010-12-21 54055.2 47
1729 2010-12-22 8241.6 47
1730 2010-12-23 8847.6 47
1731 2010-12-29 67872 47
1732 2010-12-29 38784 47
1733 2011-7-20 147621.6 47
1734 2011-8-18 1586463.2 47
1735 2011-9-21 95504.7 47
1736 2011-11-1 6297028.42 47
1737 2011-12-21 68801 47
1738 2011-1-6 1009 48
1739 2011-8-29 68400 48
1740 2011-9-2 119700 48
1741 2011-11-7 2160 48
1742 2011-11-18 158400 48
1743 2011-12-5 2100 48
1744 2011-12-7 32400 48
1745 2011-12-19 21420 48
1746 2012-1-5 8600 48
1747 2012-1-10 13500 48
1748 2011-4-13 21060 50
1749 2011-8-18 30270 50
1750 2011-6-22 25380 51
1751 2011-7-25 33840 53
1752 2011-9-13 28980 53
1753 2012-1-17 34820 53
1754 2011-1-20 18480 54
1755 2011-1-28 189000 54
1756 2011-3-8 52290 54
1757 2011-4-12 294210 54
1758 2011-5-3 32700 54
1759 2011-5-19 46620 54
1760 2011-5-23 61110 54
1761 2011-6-30 44310 54
1762 2011-8-29 34650 54
1763 2011-10-25 36060 54
1764 2011-1-19 92460 56
1765 2011-3-4 186300 56
1766 2011-4-8 109020 56
1767 2011-5-17 190440 56
1768 2011-6-3 91580 56
1769 2011-10-24 79290 56
1770 2011-12-14 393300 56
1771 2012-1-11 160080 56
1772 2011-1-10 2668.2 58
1773 2011-3-11 5175.9 58
1774 2011-5-12 8589.36 58
1775 2011-10-27 10672.8 58
1776 2011-3-11 3674.1 60
1777 2011-6-16 5051.52 60
1778 2011-8-8 36203.82 60
1779 2011-10-25 43087.32 60
1780 2011-2-23 7776 61
1781 2011-8-10 6900 61
1782 2011-7-20 250000 62
1783 2011-10-26 94436 67
1784 2011-12-20 149644.8 67
1785 2011-6-24 11794.87 69
1786 2011-7-8 80000 69
1787 2011-8-23 8000 69
1788 2011-9-8 2005.13 69
1789 2011-11-29 2840 69
1790 2012-1-12 3120 69
1791 2010-12-8 17307 70
1792 2011-6-27 2761.2 70
1793 2011-6-27 5817.6 70
1794 2011-6-27 10062 70
1795 2011-6-27 2671.2 70
1796 2011-8-29 343143.86 70
1797 2011-8-31 176857.46 70
1798 2011-9-6 10665.6 70
1799 2011-12-12 10544.4 70
1800 2011-12-12 15756 70
1801 2011-12-12 11514 70
1802 2010-12-17 7272 72
1803 2011-6-7 14788.8 73
1804 2011-11-1 218299.8 73
1805 2011-11-26 184951.2 73
1806 2011-12-14 47779.2 73
1807 2011-12-27 42899.2 73
1808 2010-12-21 54400 75
1809 2010-9-13 200000 78
1810 2010-11-24 210660 78
1811 2010-12-21 60000 78
1812 2011-1-31 80000 78
1813 2011-8-23 57410 78
1814 2011-8-31 30000 78
1815 2011-12-8 20000 78
1816 2011-12-19 30000 78
1817 2010-12-29 10920 81
1818 2011-6-29 15600 81
1819 2011-11-26 42508.8 81
1820 2011-12-21 11040 83
1821 2011-11-28 16800 87
1822 2010-11-10 130000 89
1823 2011-1-24 50000 89
1824 2011-7-25 25500 91
1825 2010-1-22 23782 92
1826 2010-12-10 376944 92
1827 2011-1-26 1000000 92
1828 2011-3-17 883068 92
1829 2011-3-23 37260 92
1830 2011-5-18 234600 92
1831 2011-5-27 122750 92
1832 2011-8-30 79392 92
1833 2011-9-21 10098 92
1834 2011-12-30 357420 92
1835 2011-1-18 152460 93
1836 2011-3-1 143640 93
1837 2011-3-1 287280 93
1838 2011-3-14 89376 93
1839 2010-12-24 2597750 94
1840 2011-1-25 2572600 94
1841 2011-1-29 3000000 94
1842 2011-4-27 300000 94
1843 2011-5-3 1861050 94
1844 2011-6-10 2000000 94
1845 2011-6-19 3000000 94
1846 2011-8-18 1100000 94
1847 2011-9-2 1000000 94
1848 2011-9-2 1405500 94
1849 2011-11-12 999900 94
1850 2011-11-19 2000000 94
1851 2011-12-16 2447250 94
1852 2011-12-19 1600000 94
1853 2010-10-30 20960 95
1854 2011-1-1 22688 95
1855 2011-1-1 377312 95
1856 2011-5-27 100000 95
1857 2011-11-29 250000 95
1858 2010-12-16 885780 96
1859 2010-12-24 335664 96
1860 2011-5-10 245088 96
1861 2010-9-27 1190000 97
1862 2011-5-31 228000 97
1863 2011-5-1 142560 98
1864 2011-9-26 70224 99
1865 2011-7-21 41580 100
1866 2010-12-28 20000 101
1867 2011-1-28 16600 101
1868 2011-6-17 109800 101
1869 2011-7-20 14640 101
1870 2011-1-19 65280 105
1871 2011-5-25 140352 105
1872 2011-7-26 79080 105
1873 2012-1-10 56940 105
1874 2010-10-22 157544 107
1875 2010-12-20 263718 107
1876 2011-8-10 473940 107
1877 2011-11-30 810648 108
1878 2011-11-30 979776 108
1879 2010-12-23 122640 112
1880 2011-2-1 63238 112
1881 2011-9-15 200000 112
1882 2012-1-21 274860 112
1883 2011-3-4 23400 114
1884 2011-2-28 91080 116
create table djjx
(
djbh varchar(20), --单据编号 key
spid varchar(20), --商品ID
shl int --数量
)
--其中二行数据:
insert into djjx values('dj0001','sp0001',300)
insert into djjx values('dj0001','sp0002',100)
--表二:
create table sphwph
(
spid varchar(20), --商品ID key
hw varchar(20), --货位 key
ph varchar(20), --批号 key
shl int --数量
)
其中几行数据:
insert into sphwph values('sp0001','hw0001','ph001',50)
insert into sphwph values('sp0001','hw0001','ph002',40)
insert into sphwph values('sp0001','hw0002','ph002',90)
insert into sphwph values('sp0001','hw0003','ph003',500)
insert into sphwph values('sp0001','hw0004','ph005',1000)
insert into sphwph values('sp0002','hw0001','ph006',90)
insert into sphwph values('sp0002','hw0002','ph009',100)
/*
求得到如下结果或一张临时表:
djbh spid hw ph shl
dj0001 sp0001 hw001 ph001 50
dj0001 sp0001 hw001 ph002 40
dj0001 sp0001 hw002 ph002 90
dj0001 sp0001 hw003 ph003 120
dj0001 sp0002 hw001 ph006 90
dj0001 sp0002 hw002 ph009 10
*/
;with t
as(
select id=row_number()over(partition by a.spid order by getdate()),
a.djbh,a.spid,a.shl as total,b.hw,b.ph,b.shl
from djjx a inner join sphwph b on a.spid=b.spid
),
m as(
select id,djbh,spid,(total-shl) as total,hw,ph,shl from t where id=1
union all
select a.id,a.djbh,a.spid,b.total-a.shl,a.hw,a.ph,a.shl from t a
inner join m b on a.id=b.id+1 and a.spid=b.spid
)
select djbh,spid,hw,ph,
case when total<0 then shl-ABS(total) else shl end as shl
from m where case when total<0 then shl-ABS(total) else shl end>0 order by spid,id
/*
djbh spid hw ph shl
dj0001 sp0001 hw0001 ph001 50
dj0001 sp0001 hw0001 ph002 40
dj0001 sp0001 hw0002 ph002 90
dj0001 sp0001 hw0003 ph003 120
dj0001 sp0002 hw0001 ph006 90
dj0001 sp0002 hw0002 ph009 10
*/有点像先进先出的东东。实在不想写，测试数据整的我机器都卡了
然后用游标进行处理。
当a表金额在b表能够select 找到相同的写此条对账成功标志。
当金额在，b表中找不到，就将此记录写入一个临时表a。记录金额和时间。
然后继续对账。循环完成后，
将b表中没有对账标志的记录写入临时表b 记录金额和时间。这时候得到一个没有对账匹配的临时表a，和临时表b
然后按日期汇总之后再对账一次(这主要是处理一天有多条，但总金额相等的情况。)最后剩下实在不能对账的就列出来让客户手工对账。
id t1_max_rowid date s t2_rowid balance
----------- ------------ ----------------------- ----------- ----------- ----------------------
3 3640 2010-03-30 00:00:00.000 125068 1629 141648.56
3 3640 2010-03-30 00:00:00.000 125068 1630 550464.56
4 3641 2011-12-01 00:00:00.000 22320 1631 0